Archives of the TeradataForum
Message Posted: Wed, 07 May 2008 @ 14:38:57 GMT
In the old days we would be required to use n-1 SUBSTRING operations nested together to get past the first several occurances as POSITION found each occurance. However, the WHERE needed to do same type of processing to verify the string contained that many occurances. It got really ugly at the 3rd occurance.
Today, a Recursive query can be written to find the occurance, if it exists. It is much cleaner but not necessarily an easy implementation. You need to track the location of each occurance, the sequence of each occurance and total length of the data so that you know when to stop looking for more occurances. But, it works when you get it all put together, something like:
with recursive dot (loc,string,lvl) as (sel position('a' in first_name||' '||last_name) p,first_name||' '||last_name,1 from student_table where p>0 union all sel loc+position('a' in substring(string from loc+1)) p,string,lvl+1 from dot where p-loc > 0 and lvl < 3) /* limits search to 2nd occurance */ sel * from dot where lvl=2 /* returns only the second occurance of 'a' in the data */ order by 2,3
Hope this is helpful.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 27 Dec 2016|