All Forums Database
ruyeh00 9 posts Joined 09/13
06 Nov 2014
Dealing with trailing whitespace

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.

dnoeth 4628 posts Joined 11/04
07 Nov 2014

Those spaces were no real spaces (hex 20), run a CHAR2HEXINT(col) to see the actual values

Dieter

ruyeh00 9 posts Joined 09/13
07 Nov 2014

Ran the above command, and this is the result: Char2HexInt(col)
4743343531343030303239A0A0A0A0  
 
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?

ruyeh00 9 posts Joined 09/13
07 Nov 2014

Internet searches provided us with the following resolution:

SELECT col_nm1,
col2,
CHAR2HEXINT(col2) ,
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
FROM mytable
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.

dnoeth 4628 posts Joined 11/04
09 Nov 2014

What's you Teradata release?
Check if Oracle's RTRIM function is available, which allows multiple characters to be trimmed:

RTRIM(col2, '20090A0B0DA0'xc)

 

Dieter

ruyeh00 9 posts Joined 09/13
11 Nov 2014

Version is Teradata 14.10.02.11, will give that a try.

You must sign in to leave a comment.