Archives of the TeradataForum

Message Posted: Wed, 31 Dec 2003 @ 21:01:53 GMT

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

The following works and you can set it up to expect as many or as few '$' as you want. if it doesn't find any '$', it'll just return a NULL string. you could even change that to return a meaningful string like "no $ found within substring" or whatever. of course you'll probably need to change this so that the subquery is reading in a table variable from the outer query instead of a string literal. heres the code :

sel  b.outstring1, b.outstring2

from
(select
(case
     when mindex('FDB$H$03121622FDBPKG1_ETL', '$') > 0
         then substring('FDB$H$03121622FDBPKG1_ETL' from 1 for
mindex('FDB$H$03121622FDBPKG1_ETL', '$') - 1)
     else NULL
END)  AS OUTSTRING1,

(case
     when mindex('FDB$H$03121622FDBPKG1_ETL', '$') > 0
         then substring('FDB$H$03121622FDBPKG1_ETL' from
mindex('FDB$H$03121622FDBPKG1_ETL', '$') + 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) as b

hope this is helpful,

-scott t.

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