Home Page for the TeradataForum

Archives of the TeradataForum

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

  <Prev Next>   <<First <Prev

Subj:   Re: Pattern matching in Teradata SQL
From:   Michael Larkins

Hi Andri:

You asked:

  However, for other problems Royson's idea could be promising. Is there a way to find the position of the nth character in a string?  

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
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016