Archives of the TeradataForum

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

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

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>>