CODEWITHSUNDEEP
teradata
Aleksei Nikolaevich
Aleksei Nikolaevich

Reputation: 325

How to parse a numeric value in a string?

Teradata question. I need to select those rows that have more than 4 consecutive digits for column mycolumn

I tried the following

SELECT  id, SUBSTR( mycolumn, ((POSITION( '3' IN CHAR2HEXINT(TRANSLATE( mycolumn USING UNICODE_TO_LATIN) ))/2)+1),  5) mycolumndigits
FROM mytable
WHERE TRIM(mycolumndigits+' ') LIKE '_ _ _ _ _ '

Sometimes I would get an untranslatable character exception, other time bad characted exception

Please help me to improve it

(PS: I am not allowed to use Oracle functions (otranslate etc.). Also i cannot create stored procedures and functions. DBA won't give me access)

Upvotes: 0

Views: 1305

Answers (1)

dnoeth
dnoeth

Reputation: 60462

If the digits are always found at the end of the string this should work: Check if the last five chars are digits.

cast(char2hexint((substring( mycolumn from (char_length(mycolumn) - 4)))) as char(10)) like '3_3_3_3_3_'

Upvotes: 1

Related Questions