Results 1 to 4 of 4

Thread: Fetching values

  1. #1
    Join Date
    Sep 2010
    Posts
    153

    Smile Unanswered: Fetching values

    Hi Guys,

    In a column, i have a value i.e '---------The request was started by Hitesh on Effective Date : 03/03/2010. It was changed by ritesh on Effective Date : 02/02/2012. it works fine now ------------'

    All i need is twi effective dates,
    Output should be

    Effective Date : 03/03/2010, Effective Date : 02/02/2012

    or

    Effective Date : 03/03/2012 Effective Date : 02/02/2012

    Anyone is fine. I want these two dates in a row. There is more than two dates in a value.


    Thanks in advance. It's very urgent and help will be greatly appreciated.

    Thanks

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    I would think feeding those values into a PERL script, or maybe writing a CLR function to parse the data would be best. String operations are not optimized in SQL Server. The data model may need some updating, too. Why are all updates lumped into one field?

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    As MCroley suggested, I'd dump the data to a text file and use PowerShell or Perl to process it.

    Using SQL to do this strikes me as using the wrong tool for the job, kind of like using a hammer to peel oranges... You can acheive the end-goal, but the process is hideous and the results are sub-optimal.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Aug 2012
    Posts
    45
    I certainly agree with the above posts in that this is a poor data model, but maybe the OP is stuck with it. In any event, performance issues aside, here is a TSQL solution. I got the Tally table CTE script from a post by JM on another forum, but a permanent numbers table is a handy tool. Anyhow, try this...
    Code:
    --==== Drop the temp test table if it exists
    IF OBJECT_ID('tempdb..#test','u') IS NOT NULL
    	DROP TABLE #test;
    
    --==== create a temp test table.  PK field is assumed
    	-- as all tables should have one. 
    CREATE TABLE #test
    	(
    	 PK INT IDENTITY(1,1),
    	 TEXT_COL VARCHAR(1000)
    	 );
    
    --==== Insert some values
    INSERT INTO #test
    VALUES('---------The request was started by Hitesh on Effective Date : 03/03/2010. It was changed by ritesh on Effective Date : 02/02/2012. it works fine now ------------'),
    	  ('---------The request was started by Hitesh on Effective Date : 03/04/2010. It was changed by ritesh on Effective Date : 02/03/2012. it works fine now ------------')
    ;
    WITH T1 AS (
    			SELECT 1 AS N UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
    			SELECT 1 AS N UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1  
    			),
    	 T2 AS (SELECT a.N FROM T1 a, T1 b),
    	 T3 AS (SELECT a.N FROM T2 a, T2 b),
    	 Tally AS (SELECT	N = ROW_NUMBER() OVER(ORDER BY N) FROM T3)
    
    SELECT
    	PK,
    	MAX(CASE WHEN ROWNUM = 1 THEN DT_VAL ELSE '' END) + ', ' +
    	MAX(CASE WHEN ROWNUM = 2 THEN DT_VAL ELSE '' END) AS Result
      FROM
    	(
    	SELECT
    		PK,
    		SUBSTRING(a.TEXT_COL,t.n,27) DT_VAL,
    		ROW_NUMBER() OVER (PARTITION BY PK ORDER BY t.N) AS ROWNUM
    	  FROM #test a, Tally t
    	 WHERE t.n <= LEN(a.TEXT_COL)
    	   AND SUBSTRING(a.TEXT_COL,t.N,14) = 'Effective Date'
    	) t1 
     GROUP BY PK
    Now, there are probably more efficient ways to do this, but I would not say this method is overly complicated. I find that SQL Server is the perfect tool for manipulating text or Excel files for one time deals like this. One huge caveat here is that we are only looking for two instances of 'Effective Date'. Any more than that would require some further manipulation.

    Greg

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •