TRIM/DROP non-printable trailing CHARACTERS

Database
N/A

TRIM/DROP non-printable trailing CHARACTERS

Hello

Need help in cleaning up some rogue trailing white spaces on a table. Below is the situation:
TABLE_A (
myPK INTEGER,
myVarCol VARCHAR(30),
myDesc CHAR(40)
)

Some of the rows have white spaces (non-printable character) on the myVarCol. The following SQL below will not return any row:
SEL *
FROM table_a
WHERE myVarCol = 'DYNAMICTABLEA';
SEL *
FROM table_a
WHERE TRIM(myVarCol) = 'DYNAMICTABLEA';

But the SQL below will work:
SEL *
FROM table_a
WHERE TRIM(myVarCol) LIKE 'DYNAMICTABLEA%';

Upon checking, there's and embeded carriage return on the myVarCol field. I don't know how many rows have this problem, but I want to clean it up so that the none-printable trailing spaces are removed. The SQL below didn't work:
UPDATE table_a
SET myVarCol = TRIM(myVarCol);

Help please.

Thanks,
Ghed
4 REPLIES

Re: TRIM/DROP non-printable trailing CHARACTERS

Try using CHAR2HEXINT function in the where clause.

More information can be obtained in
N/A

Re: TRIM/DROP non-printable trailing CHARACTERS

Hi,

We are having a similar issue. I tried to check the actual value using char2hexint function.

SEL val1, CHAR2HEXINT(val1) AS char2hexint_val1
FROM
mytable;

val1 char2hexint_val1
9 3900

The value below has some trailing spaces. TRIM() does not remove this.
9

I tried to eliminate the trailing white spaces using the below query, but unsuccessful.

SELECT
val1,
CHAR2HEXINT(val1) ,
CASE SUBSTRING(CHAR2HEXINT(val1) FROM 1 FOR 1)
WHEN '0' THEN 0
WHEN '1' THEN 1
WHEN '2' THEN 2
WHEN '3' THEN 3
WHEN '4' THEN 4
WHEN '5' THEN 5
WHEN '6' THEN 6
WHEN '7' THEN 7
WHEN '8' THEN 8
WHEN '9' THEN 9
WHEN 'A' THEN 10
WHEN 'B' THEN 11
WHEN 'C' THEN 12
WHEN 'D' THEN 13
WHEN 'E' THEN 14
WHEN 'F' THEN 15
END * 16 +
CASE SUBSTRING(CHAR2HEXINT(val1) FROM 2 FOR 1)
WHEN '0' THEN 0
WHEN '1' THEN 1
WHEN '2' THEN 2
WHEN '3' THEN 3
WHEN '4' THEN 4
WHEN '5' THEN 5
WHEN '6' THEN 6
WHEN '7' THEN 7
WHEN '8' THEN 8
WHEN '9' THEN 9
WHEN 'A' THEN 10
WHEN 'B' THEN 11
WHEN 'C' THEN 12
WHEN 'D' THEN 13
WHEN 'E' THEN 14
WHEN 'F' THEN 15
END AS asciival
FROM
mytable
WHERE asciival = 9 -- Horizontal tab
OR asciival = 11 -- Vertical tab
OR asciival = 13 -- Carriage Return
OR asciival = 10 -- New Line
OR asciival = 32 -- Spaces

Please advise.
Thanks
Toad
N/A

Re: TRIM/DROP non-printable trailing CHARACTERS

If you only need to remove a newline from the end of the line, you can use the following:

Select myPK, myVarCol,myDesc,Char2HexInt(myVarCol)
, Char2HexInt(substr(myVarCol,1,Chars(myVarCol)-1))
From Table_A
Where Substr(myVarCol,chars(trim(myVarCol)),1) = '0a'XC
Order by 1
;

If you want to remove all the carriage returns from a line, you will need the oreplace UDF. (Use these if you also want to return tabs/ newline, etc.
N/A

Re: TRIM/DROP non-printable trailing CHARACTERS

Thanks Jimm,

I had modified your query to check if there is any of the unwanted characters exists at any position of the column.

SELECT
MyVarCol
, CHAR2HEXINT(MyVarCol)
, INDEX(MyVarCol, '00'XC ) AS BLANKLID
, INDEX(MyVarCol, '20'XC ) AS SPACEID
, INDEX(MyVarCol, '09'XC ) AS HTID
, INDEX(MyVarCol, '0A'XC ) AS LFID
, INDEX(MyVarCol, '0B'XC ) AS VTID
, INDEX(MyVarCol, '0D'XC ) AS CRID

FROM MyTable
WHERE
(
INDEX(MyVarCol, '00'XC ) > 0 -- blank
OR INDEX(MyVarCol, '20'XC ) > 0 -- Spaces
OR INDEX(MyVarCol, '09'XC ) > 0 -- Vertical tab
OR INDEX(MyVarCol, '0A'XC ) > 0 -- Line Feed
OR INDEX(MyVarCol, '0B'XC ) > 0 -- Vertical tab
OR INDEX(MyVarCol, '0D'XC ) > 0 -- Carrriage Return
)
;

Thanks,
Toad