Is not [] supported by like pattern in TeraData?

Database
Fan

Is not [] supported by like pattern in TeraData?

i cannot understand why aField like '[0-9][0-9]' does not work.
5 REPLIES
Teradata Employee

Re: Is not [] supported by like pattern in TeraData?

You are probably thinking of regular expressions which are not supported in Teradata. The only wild-card options you can use in the LIKE pattern match are % (any match of zero or more characters) and _ (exactly one arbitrary character). I think Oracle now supports regular expressions but this is not the case for Teradata.

Re: Is not [] supported by like pattern in TeraData?

jeff_o,

Is there any work around for this? i need to find rows that have a column values that match a pattern, say, CC99999 (AA12345, ZA15674 etc.) or C9C999(X1Y678). Here C represents a character and 9 represents a digit. How do I find the rows? Can it be done without using regular expressions?
Enthusiast

Re: Is not [] supported by like pattern in TeraData?

I don't think Teradata has this capability.
We may probably think of a UDF.

I had a SQL that I had posted some time back.
This will find whether a column value is numeric or non-numeric.
You may modify this SQL's functionality to check character by character.

SEL
chr -- CHAR type
,CASE WHEN (POSITION(' ' IN TRIM(chr)) > 0) OR (UPPER(TRIM(chr)) (CASESPECIFIC) <> LOWER(TRIM(chr)) (CASESPECIFIC))
THEN 'Non-Numeric'
ELSE 'Numeric' End
FROM test
ORDER BY 2,1;

Vinay
Enthusiast

Re: Is not [] supported by like pattern in TeraData?

I changed the SQL a little bit

SEL
chr -- CHAR type
,SUBSTRING(chr FROM 1 for 3) AS chr_val
,SUBSTRING(chr FROM 4 for 4) AS num_val
,CASE WHEN (UPPER(TRIM(chr_val)) (CASESPECIFIC) <> LOWER(TRIM(chr_val)) (CASESPECIFIC))
AND (UPPER(TRIM(num_val)) (CASESPECIFIC) = LOWER(TRIM(num_val)) (CASESPECIFIC))

THEN 'Pattern Match'
ELSE 'Unknown Pattern' End AS Pattern
FROM test
ORDER BY 2,1;

And here's the result set.

chr chr_val num_val Pattern
1123 112 3 Unknown Pattern
1233 123 3 Unknown Pattern
1asd 1as d Unknown Pattern
abc1234 abc 1234 Pattern Match
mno6787 mno 6787 Pattern Match
rst1045 rst 1045 Pattern Match
xyz1473 xyz 1473 Pattern Match

I have assumed that first three characters are always CHAR type and next four characters to be INT type.

Seems to work.

Vinay

Re: Is not [] supported by like pattern in TeraData?

Thanks for the idea, Vinay. That should work for values of fixed format. But I have to deal with strings with any format. I think i must ponder over your script to get it working for me. Thanks again.