Archives of the TeradataForum

Message Posted: Fri, 02 Jan 2004 @ 14:08:15 GMT


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

Subj:		Re: Extracting characters from a string

From:		Scott Thompson

I have a Character Strings like 'FDB$H$03121622FDBPKG1_ETL' and 'COGNOS$M$03122521COGNOSIWR' from that i need FDB and H, COGNOS and M, i.e. those characters before $. How do I get them.

Here is a more practical application of the solution i sent earlier :

SEL X.PRIMKEY, X.INPSTRING, X.OUTSTRING1, X.OUTSTRING2, X.OUTSTRING3

FROM (


select A.PRIMKEY AS PRIMKEY,
       A.INPSTRING AS INPSTRING,
(case
     when mindex(A.INPSTRING, '$') > 0
         then substring(A.INPSTRING from 1 for mindex(A.INPSTRING, '$') - 1)

     else NULL
END)  AS OUTSTRING1,

(case
     when mindex(A.INPSTRING, '$') > 0
         then substring(A.INPSTRING from mindex(A.INPSTRING, '$') + 1)
     else NULL
END)  AS REMAINDER1,


(case
     when mindex(REMAINDER1, '$') > 0
         then substring(REMAINDER1 from 1 FOR mindex(REMAINDER1, '$') -1)
     else NULL
END)  AS OUTSTRING2,

(case
     when mindex(REMAINDER1, '$') > 0
         then substring(REMAINDER1 from mindex(REMAINDER1, '$') + 1)
     else NULL
END)  AS REMAINDER2,

(case
     when mindex(REMAINDER2, '$') > 0
         then substring(REMAINDER2 from 1 FOR mindex(REMAINDER2, '$') -1)
     else NULL
END)  AS OUTSTRING3,

(case
     when mindex(REMAINDER2, '$') > 0
         then substring(REMAINDER2 from mindex(REMAINDER2, '$') + 1)
     else NULL
END)  AS REMAINDER3

FROM


(SEL C.* FROM (SEL '1' AS PRIMKEY,
         'FDB$H$03121622FDBPKG1_ETL' AS INPSTRING)  AS C

UNION

SEL D.* FROM (SEL '2' AS PRIMKEY,
         'COGNOS$M$03122521COGNOSIWR' AS INPSTRING)  AS D)

AS A(PRIMKEY, INPSTRING)

) AS X

ORDER BY 1

Here is the answer set (from queryman) :

PRIMKEY INPSTRING                 OUTSTRING1    OUTSTRING2
OUTSTRING3
1         FDB$H$03121622FDBPKG1_ETL     FDB                H       ?
2         COGNOS$M$03122521COGNOSIW     COGNOS       M     ?

Note: tables a, c, and d were built just to demonstrate the extraction code.

in reality, your data will probably be coming from a pre-existing table and thus your implementation of the above code should be alot less clunky than that displayed in the above example.

-scott t.


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

Attachments

Library

Quick Reference