Archives of the TeradataForum
Message Posted: Thu, 04 Apr 2013 @ 23:00:19 GMT
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;
Johannes de Wet
|Copyright 2016 - All Rights Reserved|
|Last Modified: 24 Jul 2020|