Database

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

08-14-2008
10:10 PM

08-14-2008
10:10 PM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

08-15-2008
10:52 AM

08-15-2008
10:52 AM

Try using CHAR2HEXINT function in the where clause.

More information can be obtained in

More information can be obtained in

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

08-31-2009
01:04 AM

08-31-2009
01:04 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

08-31-2009
04:19 PM

08-31-2009
04:19 PM

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.

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

09-01-2009
01:27 AM

09-01-2009
01:27 AM

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

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

Copyright © 2004-2015 Teradata Corporation. Your use of this Teradata website is governed by the Privacy Policy and the Terms of Use, including your rights to materials on this website, the rights you grant to your submissions to this website, and your responsibilities regarding your conduct on this website.

The Privacy Policy and Terms of Use for this Teradata website changed effective September 8, 2016.