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...
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
2 Answers
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
I found using the function TRIM to work better. I think the error is a result of leading/trailing spaces.
Elena Rostova holds a Master's degree in Public Health Journalism. She covers groundbreaking medical research, holistic wellness trends, mental health awareness, and nutritional science.
