Substitute for "LIKE" in teradata

Database

Substitute for "LIKE" in teradata

Hi All,

Could anyone please help me and suggest a function/ expression/ operator in Teradata to write a query that would return a set of rows which include any single character between 'A-P'.

I'm looking for a operator like 'LIKE'.

I have tried regexp_like and regexp_similar but the results were not as expected.

Let me know in case any more details needed.

Thanks.

2 REPLIES
Senior Apprentice

Re: Substitute for "LIKE" in teradata

Standard SQL LIKE only supports _ & %, using ranges is characters is an extension (e.g. SQL Server).

What did you try?

There's no regexp_like in Teradata, but regexp_similar works fine:

WHERE REGEXP_SIMILAR(column, '.*[A-P].*', 'i') = 1

switch to 'c' for a case-sensitive search

Re: Substitute for "LIKE" in teradata

Hi Dieter,

The condition given by you using REGEXP_SIMILAR worked for me. I was missing out the '.*' part.

i had tried below query which worked for me:

 

select

case when SUBSTRING(column_name from 1 for 1) between 'A' and 'P' then column_name

end alias

from table

where alias is not null;

 

but the approach given by you looks good.

Thanks.