We recently had a situation where some rows of data contained trailing spaces ie. 'ABC123 ', when we did a trim on the field it didn't remove the trailing space as was expected. The source data was loaded from an Excel spreadsheet into a TD table. To be exact it was 4 spaces.
My question is how can we search for 1,2,3,4 or more spaces whether it be leading or trailing spaces?
I tried LIKE '% %', but that didn't return anything. It appears to be treating them as a special character or something.
Ran the above command, and this is the result: Char2HexInt(col)
It appears that the actual values are the A0A0A0A0 at the end. Any advice on how we can prevent/remove such values from remaining in our dataset?
Internet searches provided us with the following resolution:
INDEX(col2, '00'XC ) AS BLANKLID,
INDEX(col2, '20'XC ) AS SPACEID,
INDEX(col2, '09'XC ) AS HTID,
INDEX(col2, '0A'XC ) AS LFID,
INDEX(col2, '0B'XC ) AS VTID,
INDEX(col2, '0D'XC ) AS CRID,
INDEX(col2, 'A0'XC ) AS LFNULLID
WHERE ( INDEX(col2, '00'XC ) > 0
OR INDEX(col2, '20'XC ) > 0 --blank Spaces
OR INDEX(col2, '09'XC ) > 0 --Vertical tab
OR INDEX(col2, '0A'XC ) > 0 -- Line Feed
OR INDEX(col2, 'A0'XC ) > 0 -- Line Feed & NULL
OR INDEX(col2, '0B'XC ) > 0 -- Vertical tab
OR INDEX(col2, '0D'XC ) > 0 ) -- Carrriage Return
group by 1,2,3;
The above query results in an Error 3963 - Bad argument type to Char2hexint function. Guessing that we need to Cast the field?
But would like to know if there is a better and/or more efficient method to find these "whitespace" hexadecimal characters.
What's you Teradata release?
Check if Oracle's RTRIM function is available, which allows multiple characters to be trimmed: