Archives of the TeradataForum

Message Posted: Wed, 07 May 2008 @ 14:38:57 GMT

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

Hi Andri:

You asked:

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.

Michael Larkins
Certified Teradata Master
Certified Teradata SQL Instructor

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