Dealing with trailing whitespace

Database
Enthusiast

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.

Tags (2)
5 REPLIES
Senior Apprentice

Re: Dealing with trailing whitespace

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

Enthusiast

Re: Dealing with trailing whitespace

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?

Enthusiast

Re: Dealing with trailing whitespace

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.

Senior Apprentice

Re: Dealing with trailing whitespace

What's you Teradata release?

Check if Oracle's RTRIM function is available, which allows multiple characters to be trimmed:

RTRIM(col2, '20090A0B0DA0'xc)
Enthusiast

Re: Dealing with trailing whitespace

Version is Teradata 14.10.02.11, will give that a try.