How to use regex in Teradata or 'LIKE' operator in teradata

Database

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?

Tags (2)
5 REPLIES
Enthusiast

Re: How to use regex in Teradata or 'LIKE' operator in teradata

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,

Senior Apprentice

Re: How to use regex in Teradata or 'LIKE' operator in teradata

Hi Raja,

this will fail for data like '1a1'.

Using a regular expression it's:

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

Re: How to use regex in Teradata or 'LIKE' operator in teradata

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

Thanks ,

Re: How to use regex in Teradata or 'LIKE' operator in teradata

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

Senior Apprentice

Re: How to use regex in Teradata or 'LIKE' operator in teradata

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