Understanding like and substring

Database

Understanding like and substring

Hello! This is my first post here and I have to say that the forums here have been incredibly helpful for me. I'm still very new to Teradata, and I'm still struggling with a problem in how "LIKE" is handling strings in some of my queries. I'll lay out what I'm trying to accomplish:

I'm trying to search within a string for a block of eight numbers, which always begin at the start of the string. If the first eight characters are not all numbers, it should return nothing.

How I approached this was to do a substring to get it down to eight characters and then check to see if each character was in the range 0-9:

case when substring(String_Cell from 1 for 8) not like '[0-9],[0-9],[0-9],[0-9],[0-9],[0-9],[0-9],[0-9],[0-9]' then '' else substring(String_Cell from 1 for 8)

I also tried this, but got the same result:

case when substring(String_Cell from 1 for 8) not like '[0-9]' then '' else substring(String_Cell from 1 for 8)

My problem is that this when trying either of these I don't get any results, everything returns the empty string, even the ones that start with eight numbers. Eliminating the "LIKE" statement and simply going with the else statement includes results that don't make sense.

Am I understanding the usage of "LIKE" correctly? It appears as though "LIKE" is ID'ing whitespace as being "like [0-9]". 

For example "Trv Chd Care" returns "Trv Chd ".

I've looked at other ways of doing this other ways, like with REG_EXP, but due to systems limitations I think that I'll have to do it the way I've outlined above.

Thanks for the help!

2 REPLIES
Senior Apprentice

Re: Understanding like and substring

Teradata's LIKE is the same as Standard SQL's LIKE, only "_" and "%" are supported as wildcards. The syntax you're trying seems to be MS SQL Server...

If you're on TD14 you better go for a regex instead of CASE/LIKE:

REGEXP_SUBSTR(x, '^[0-9]{8}')

Re: Understanding like and substring

Dieter, this is precisely what I needed, I just tested it and it worked perfectly. I had tried using REGEXP before, but I must not have been using the correct syntax because I kept getting errors that said something like "cannot find function". 

I'm not sure how I got mixed up into using MySQL syntax, but I'll just chalk that up to the fact that I'm a finance guy trying to learn to code :)