Archives of the TeradataForum

Message Posted: Thu, 04 Apr 2013 @ 23:00:19 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Jerry,

If I understand your question correctly, you can probably use the INDEX function to determine the positions of your prior 14 Chars and Post 5 Chars and then Substring just what you need to pull out the Diagnosis Code.

Prior14 = Text String containing 'consistent' Prior 14 Chars

Post5 = Text String containing 'consistent' Post 5 Chars

MyFile = Data from your Input File

You should be able to run the SQL below as-is to get an idea of how this could possibly work for you. This only works for detecting the first instance of a 'Diagnosis Cd'. If you are expecting many diagnosis codes in the same column, you would need to write something more recursive than my example below, to find all the instances of the codes.

     SELECT
         '11111111111111' AS Prior14
        ,'22222'          AS Post5
        ,'12345611111111111111diagnosis22222987653....' AS MyFile
        ,
        CASE
           --Only attempt to pull the Diagnosis Cd if it is preceded by specific 14 chars and followed by specific characters
           WHEN INDEX(MyFile,Prior14) > 0 AND INDEX(MyFile,Post5) > 0 THEN
            SUBSTR(MyFile,CHAR(Prior14) + INDEX(MyFile,PRIOR14),
     INDEX(MyFile,Post5) - INDEX(MyFile,Prior14) - CHAR(Prior14))
            ELSE ''
        END AS Diagnosis_Cd;

Thanks,

Johannes de Wet
Unum

	<Prev	Next>		<<First	<Prev	Next>	Last>>