hopefully you want this SQL to split the data in two columns, a VARCHAR is definitely plain wrong.
CASE WHEN POSITION(' ' IN TRIM(price)) > 0 THEN SUBSTRING(TRIM(price) FROM POSITION(' ' IN TRIM(price))+1) END AS currency
You don't need LENGTH, before TD14 it's an ODBC function, in TD14 it's a fastpath-UDF, maybe this causes the error?
What's your TD release?
Can you please tell what issue exactly you are facing?, I have tried the same code with a little change and it is working fine. The code I used is
CASE WHEN TRIM(price) NOT LIKE '% %' THEN NULL ELSE
TRIM(SUBSTR(TRIM(price),POSITION (' ' IN TRIM(price)),CHAR_LENGTH(TRIM(price)))) END AS currency
Else you can use
TRANSLATE((price (VARCHAR(50))) USING UNICODE_TO_LATIN)
The code you gave is working fine. I am using Teradata 14.00.0417. It seems the issue was with LENGTH function i was using.
Thanks for your help
If you are trying to convert a currency string to a numeric value, then your approach is not internationalized. For example, if your string was '2 49 Swedish Krona' which includes a radix that is a SPACE (U+0020).
See the character-to-numumeric type conversion in the SQL functions document using an international format clause. The SDF needs to be setup using the tdlocaledef utility. The cast will convert to numeric without added triming, etc.