All Forums Database
netuser 1 post Joined 12/13
15 Jan 2014
How to use regex in Teradata or 'LIKE' operator in teradata

I have a table tab1 with structure as-

    columnName | datatype
          col1 | VARCHAR  
          col2 | VARCHAR
          col3 | VARCHAR
          col4 | VARCHAR
          col5 | VARCHAR

 and sample data as

    col1  | col2 | col3  | col4 | col5
    11    | aaa  | aaaa | aaaa   | 1111
    22    | bbb  | bbbb | bbbb   | 2a2s
    33    | ccc   | cccc | ccccc   | a312
    44    | ddd  | dddd | dddd   | 4444

Now i want to retrieve only those rows having col5 with only numbers ( like 1st and 4th row). How to write the query in oracle and teradata?

Raja_KT 1246 posts Joined 07/09
15 Jan 2014

Hi,
I am not very sure how you represent data like above.
 
This is what I get:

CREATE MULTISET TABLE db1.raja_test1 ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      id VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,

      name VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC)

NO PRIMARY INDEX ;

 

 

select * from db1.raja_test1

 

id              name

------------------------------------------

abc124    abc123

9124        6123

 

select * from db1.raja_test1
minus
select * from  db1.raja_test1 where trim(id) not between '0' and '9999999999999999' 

 

 

select * from  db1.raja_test1 where trim(id) not between '0' and '9999999999999999' --- shows for non-numeric.

 

Will this logic help you?

 

Cheers,

 

 

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

dnoeth 4628 posts Joined 11/04
16 Jan 2014

Hi Raja,
this will fail for data like '1a1'.
 
Using a regular expression it's:

WHERE REGEXP_SIMILAR(id, '[0-9]+', 'c')=1

 

Dieter

Raja_KT 1246 posts Joined 07/09
16 Jan 2014

Oops, I did not test it for data like '1a1'.
Thanks ,

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

sanjeevkumar044 1 post Joined 01/15
18 Aug 2016

Hi Dieter,
I would like to know more about '[0-9]+' .    request you to briefly explain imporntance of + and how does the '[0-9]+' block work.
 
 
Thank you,
Sanjeev Kumar
 

dnoeth 4628 posts Joined 11/04
18 Aug 2016

[0-9] is a range of characters and + means repeat the previous at least once.

Dieter

You must sign in to leave a comment.