TRIM trailing spaces on a varchar column

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Enthusiast

TRIM trailing spaces on a varchar column

Hello,

My table tbl has a column col which has the below definition:-
COL VARCHAR(250) CHARACTER SET UNICODE NOT CASESPECIFIC TITLE 'COL'

I am trying to trim spaces on the data in the this col. I used TRIM and what I see in the result is that it trims leading spaces but not the trailing spaces. I have tried various combinations in the query below:-

SEL COL,CHARACTER_LENGTH(COL),COL||'-',
TRIM(COL) a,CHARACTER_LENGTH(a),A||'-',
TRIM(TRAILING' ' FROM COL) B,charACTER_length(B),B||'-',
TRIM(TRAILING' ' FROM CAST(COL AS VARCHAR(2000))) C,CHARACTER_length(C),C||'-',
TRIM(TRAILING' ' FROM CAST(COL AS CHAR(2000))) D,CHARACTER_length(D) ,D||'-'
FROM TBL;

PRD_NAM Characters(PRD_NAM) (PRD_NAM||'-') a Characters(a) (a||'-') B 904 5 904 - 904 5 904 - 904
Characters(B) (B||'-') C Characters(C) (C||'-') D Characters(D) (D||'-')
5 904 - 904 5 904 - 904 5 904 -

I have come to know that Teradata doesnt trim trailing spaces on a varchar column and nothing can be done abt it. Is that true?

Regards,
Ayush Jain
8 REPLIES
Senior Apprentice

Re: TRIM trailing spaces on a varchar column

Hi Ayush,
the trailing spaces are probably no spaces (hex 20), they just look like one.

Try a CHAR2HEXINT(col) to see the actual hex value, e.g. could be '80' for LATIN.

And then use "trim (trailing '80'xc from col)"

Dieter
Enthusiast

Re: TRIM trailing spaces on a varchar column

I had actually done that!!

The Hex Values were '0000' for both the 4th and 5th position in the above col data. I checked the ascii table and it seems hex '0000' represents NULL. This makes it more confusing.

Regards,
Ayush Jain
Enthusiast

Re: TRIM trailing spaces on a varchar column

BTW, This is where I found someone mentioning that Teradata doesn't trim trailing spaces on varchar columns.

http://www.teradataforum.com/teradata/20060820_103447.htm

However, I refered the SQL Reference Manual Vol.3 and page 2-37 says:-

"Note that trailing blanks can be trimmed from a VARCHAR field."

Still wondering why does it show hex '0000' (NULL) and if its unacceptably NULL then shouldnt TRIM trim the NULL as well?

Do you suggest any place where I can see the char corresponding to the 16-bit hex?

Regards,
Ayush Jain
Senior Apprentice

Re: TRIM trailing spaces on a varchar column

TRIM by default trims hex '20' (latin) or '0020' (unicode) spaces.

For any other character you have to specify it using, e.g.
TRIM(TRAILING '00'xc FROM col)
or
TRIM(TRAILING _UNICODE '0000'xc FROM col)
will remove those nulls.

If the column is unicode and there are some other characters which look like spaces there's a
TRANSLATE(col USING unicode_to_unicode_foldspace)
to replace those with 0020.

You'll find more details in the "International Character Set Support" manual, the Unicode mappings are in UNCDUNCD.txt located in the documentation folder.

Dieter
Enthusiast

Re: TRIM trailing spaces on a varchar column

Thanks Dieter, I checked the documentation at:-
http://www.info.teradata.com/

Couldnt find the above mentioned txt file :(

Regards,
Ayush Jain
Senior Apprentice

Re: TRIM trailing spaces on a varchar column

Hi Ayush,
the official name on the info-site (and in Start_here) is "Unicode Server Character Set".

Dieter
Enthusiast

Re: TRIM trailing spaces on a varchar column

I am trying to TRIM a date field ( MNC_dt DATE FORMAT 'YY/MM/DD') as it had some trailing spaces form the source data but the resule of trim is changing the date format, any reason?

Original value is =  2/2/2006

After trim it changed to  =  06/02/02

Enthusiast

Re: TRIM trailing spaces on a varchar column

Hi,

i need help on triming columns,

am using one fexp script lik this

.

.LOGTABLE

.logon

.BEGIN EXPORT

SESSIONS 12;

.EXPORT OUTFILE ------;

MODE RECORD FORMAT TEXT;

SELECT CAST(

col||'|~|'||

col||'|~|'||

col||'|~|'||

col||'|~|'||

col||'|~|'||

col||'|~|'||

col||'|~|'||

-----------

------------

as char (1000))

from tabela

my query like this..

and expected outputis like this 

RECORD1- COL1|~|COL2

RECORD2   COL1|~|COL2

but actual result

RECORD1- COL1|~|COL2

RECORD2   COL1|~|COL2

how to remove the all space in between records

regards,

AC