All Forums Database
manaswi_patil 2 posts Joined 06/16
16 Aug 2016
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.

dnoeth 4628 posts Joined 11/04
17 Aug 2016

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

Dieter

manaswi_patil 2 posts Joined 06/16
17 Aug 2016

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.

You must sign in to leave a comment.