CODEWITHSUNDEEP
sqlwildcardteradata
Jude92
Jude92

Reputation: 169

LIKE Wild Card Search in Teradata

I have text in a column like RAPP 01. RAPP 02 upto RAPP 45 and RAPP 99.I included all these values manually in my IN statement in my WHERE clause but it slows the query as the data set in huge. I tried WHERE SUBSTR(REMARK_TXT,1,7) LIKE 'RIPA [01-45,99]' and it did not return any data. Can you please help? Thanks!

Upvotes: 0

Views: 6243

Answers (2)

jbrezik
jbrezik

Reputation: 137

You could use the following:

where column like ('RAPP %')

Which would return anything beginning with the string RAPP and a whitespace. Notice the '%' sign, this will be your wildcard. Careful on using like, especially not like and putting the wildcard at the beginning of your condition, it would have much bigger performance issues.

Upvotes: 0

JNevill
JNevill

Reputation: 50218

You could use REGEXP functionality here:

 WHERE REGEXP_SIMILAR(REMARK_TXT, '^RAPP [0-9]{2}$') = 1;

That regex matches with a string that starts with RAPP followed by a space then followed by 2 numbers and the end of the string.

Updating to deal with two number ranges (01-49) and (99). This isn't the best thing to do with regex, but it's still possible:

 WHERE REGEXP_SIMILAR(REMARK_TXT, '^RAPP ([0-4][0-9]|99)$') = 1;

This is saying a string that starts with RAPP and then ends in either a two digit number that starts with 0 through 4 OR the number 99

Upvotes: 1

Related Questions