Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Fri, 02 May 2008 @ 11:29:51 GMT


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


Subj:   Re: Pattern matching in Teradata SQL
 
From:   ulrich arndt

Hi anomy,

there are several possible ways but non elegant - an regex UDF would be the best.

SQL solutions:

1. join with a derived (or permanent table which contains all Rx or Rxy combinations. Join condition would be fact.col like '%' !! ref.col !! '%' - might cause some problems in case of a big fact table. be careful in case you have one digit and two digit the derived table - it will create wrong results, at least can...

2. generate a case statement with all possible Rx or Rxy combinations and test again it with a like condition.

     case when col like '%R1%' then 1
          when col like '%R2%' then 1
          ...
          else 0
          end

3. generate a case statement with on list of all possible Rx or Rxy combinations

     case when col like any ('%R0%','%R1%','%R2%'...,'%R9%')
               then 1
               else 0
               end
     case when col like any ('%R00%','%R01%','%R02%'...,'%R99%')
               then 1
               else 0
               end

And maybe more...

Ulrich



     
  <Prev Next>   <<First <Prev Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016