Sybase to Teradata inquiry LIKE '[0-9]'

Question

CASE
         WHEN <in_data> LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]' THEN SUBSTR(<in_data>,1,3)
ELSE '000'
END

We're doing a migration project from Sybase to Teradata, and having a problem figuring this one out :) I'm still new to Teradata.

I would like to ask the equivalent TD code for this - LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]' to Teradata

Basically, it just checks whether the digits are numeric value. Can someone give me a hint on this

Answer 1

You need to use regexp instead of like, since [0-9][0-9][0-9][0-9][0-9][0-9] is a regular expression.

To do an exact match, you need to add anchors. ie, to match the string which contains an exact 6 digit chars.

regexp '^[0-9]{6}$'

or

regexp '^[[:digit:]]{6}$'

Answer 2

You can also use REGEXP_SUBSTR to directly extract the three digits:

COALESCE(REGEXP_SUBSTR(in_data,'^[0-9]{3}(?=[0-9]{3}$)'), '000')

This looks for the first three digits and then does a lookahead for three following digits without adding them to the overall match.

^ indicates the begin of the string, '$' the end, so there are no other characters before or after the six digits. (?=...) is a so-called "lookahead", i.e. those three digits are checked, but ignored.

If there's no match the regex returns NULL which is changed to '000'.