All Forums Database
jufu 4 posts Joined 06/06
01 Jun 2006
Is not [] supported by like pattern in TeraData?

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

j355ga 100 posts Joined 12/05
01 Jun 2006

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.

Jeff

11 Sep 2006

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?

BBR2 96 posts Joined 12/04
11 Sep 2006

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.SELchr -- CHAR type,CASE WHEN (POSITION(' ' IN TRIM(chr)) > 0) OR (UPPER(TRIM(chr)) (CASESPECIFIC) <> LOWER(TRIM(chr)) (CASESPECIFIC)) THEN 'Non-Numeric' ELSE 'Numeric' EndFROM test ORDER BY 2,1;Vinay

BBR2 96 posts Joined 12/04
11 Sep 2006

I changed the SQL a little bit SELchr -- 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 PatternFROM test ORDER BY 2,1;And here's the result set.chr chr_val num_val Pattern1123 112 3 Unknown Pattern1233 123 3 Unknown Pattern1asd 1as d Unknown Patternabc1234 abc 1234 Pattern Matchmno6787 mno 6787 Pattern Matchrst1045 rst 1045 Pattern Matchxyz1473 xyz 1473 Pattern MatchI have assumed that first three characters are always CHAR type and next four characters to be INT type.Seems to work.Vinay

12 Sep 2006

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.

You must sign in to leave a comment.