CODEWITHSUNDEEP
teradata
angelcake
angelcake

Reputation: 119

Teradata error: The format or data contains a bad character

I get the error of "format or data contains a bad character" from the query below. The datatype of column1 is VARCHAR(2000). This is an example of the string 'ROC 2236 00 ROC' for column1 and the result I would like to get is '2236.00'

SELECT ORDER_NUMBER,
      CASE WHEN COLUMN1 LIKE 'ROC%' THEN CAST(OREPLACE(TRIM(COALESCE(SUBSTR('COLUMN1',5, 8),0)),' ','.' ) AS DEC(12,2) END           
FROM TABLE1
WHERE CAST(COLUMN1 AS DEC(12,2)) > 0

Upvotes: 0

Views: 37569

Answers (2)

anything_everything
anything_everything

Reputation: 81

I found using the function TRIM to work better. I think the error is a result of leading/trailing spaces.

Upvotes: 1

Bill Ansley
Bill Ansley

Reputation: 61

It is hard to test this without actual data, however at first glance, you are trying to convert the word column1 to a decimal...

and your where clause is doing a straight conversion and with sample data provided it would not like converting "ROC" to a decimal

try

SELECT 
     ORDER_NUMBER 
   , CASE WHEN COLUMN1 LIKE 'ROC%' THEN 
       CAST(OREPLACE(TRIM(COALESCE(SUBSTR(COLUMN1,5, 8),0)),' ','.' ) AS DEC(12,2)) 
     END
FROM TABLE1 
WHERE CAST(OREPLACE(TRIM(COALESCE(SUBSTR(COLUMN1,5, 8),0)),' ','.' ) AS DEC(12,2)) > 0

Upvotes: 1

Related Questions