All Forums Database
ghed 1 post Joined 08/08
14 Aug 2008
TRIM/DROP non-printable trailing CHARACTERS

HelloNeed 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_aWHERE myVarCol = 'DYNAMICTABLEA';SEL *FROM table_aWHERE TRIM(myVarCol) = 'DYNAMICTABLEA';But the SQL below will work:SEL *FROM table_aWHERE 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_aSET myVarCol = TRIM(myVarCol);Help please.Thanks,Ghed

sp230071 29 posts Joined 10/06
15 Aug 2008

Try using CHAR2HEXINT function in the where clause.More information can be obtained in

Toad 12 posts Joined 12/08
31 Aug 2009

Hi,We are having a similar issue. I tried to check the actual value using char2hexint function.SEL val1, CHAR2HEXINT(val1) AS char2hexint_val1FROMmytable;val1 char2hexint_val19 3900The 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.SELECTval1,CHAR2HEXINT(val1) ,CASE SUBSTRING(CHAR2HEXINT(val1) FROM 1 FOR 1)WHEN '0' THEN 0WHEN '1' THEN 1WHEN '2' THEN 2WHEN '3' THEN 3WHEN '4' THEN 4WHEN '5' THEN 5WHEN '6' THEN 6WHEN '7' THEN 7WHEN '8' THEN 8WHEN '9' THEN 9WHEN 'A' THEN 10WHEN 'B' THEN 11WHEN 'C' THEN 12WHEN 'D' THEN 13WHEN 'E' THEN 14WHEN 'F' THEN 15END * 16 +CASE SUBSTRING(CHAR2HEXINT(val1) FROM 2 FOR 1)WHEN '0' THEN 0WHEN '1' THEN 1WHEN '2' THEN 2WHEN '3' THEN 3WHEN '4' THEN 4WHEN '5' THEN 5WHEN '6' THEN 6WHEN '7' THEN 7WHEN '8' THEN 8WHEN '9' THEN 9WHEN 'A' THEN 10WHEN 'B' THEN 11WHEN 'C' THEN 12WHEN 'D' THEN 13WHEN 'E' THEN 14WHEN 'F' THEN 15END AS asciivalFROMmytableWHERE asciival = 9 -- Horizontal tabOR asciival = 11 -- Vertical tabOR asciival = 13 -- Carriage ReturnOR asciival = 10 -- New LineOR asciival = 32 -- SpacesPlease advise.ThanksToad

Jimm 298 posts Joined 09/07
31 Aug 2009

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_AWhere Substr(myVarCol,chars(trim(myVarCol)),1) = '0a'XCOrder 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.

Toad 12 posts Joined 12/08
01 Sep 2009

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 CRIDFROM MyTableWHERE ( INDEX(MyVarCol, '00'XC ) > 0 -- blankOR INDEX(MyVarCol, '20'XC ) > 0 -- SpacesOR INDEX(MyVarCol, '09'XC ) > 0 -- Vertical tabOR INDEX(MyVarCol, '0A'XC ) > 0 -- Line FeedOR INDEX(MyVarCol, '0B'XC ) > 0 -- Vertical tabOR INDEX(MyVarCol, '0D'XC ) > 0 -- Carrriage Return); Thanks,Toad

You must sign in to leave a comment.