Additional padding characters that are not removable with TRIM function

General
Enthusiast

Additional padding characters that are not removable with TRIM function

I have a table with column COL1 defined with datatype VARCHAR(70) and I notice that there are additional SPACE characters that are not getting removed with TRIM function. When i run the below query, I get the character length of 70 while the actual data length is less than 70 chars.

While char2hexint shows value 20 which is SPACE, using TRIM function i am unable to remove the same from this column COL1. Please help me understand why the spaces are not getting removed and the length is not displayed accurately.

SELECT

    COL1,

    char2hexint(COL1) as COL1_HEX,

     char_length(trim(COL1)),

    char_length(trim(trailing TD_SYSFNLIB.chr(20) from COL1)) as COL1_TRIM_LEN

FROM DBNAME.TABLENAME

WHERE

char_length(COL1) GT 60;

Output is as below:







COL1 COL1_HEX Characters(Trim(BOTH FROM COL1)) COL1_TRIM_LEN
FILGRASTIM                                                             46494C4752415354494D202020202020202020202020202020202020202020202020200000000000000000000000000000000000000000000000000000000000000000000000 70 70
FILGRASTIM                                                             46494C4752415354494D202020202020202020202020202020202020202020202020200000000000000000000000000000000000000000000000000000000000000000000000 70 70
FILGRASTIM                                                             46494C4752415354494D202020202020202020202020202020202020202020202020200000000000000000000000000000000000000000000000000000000000000000000000 70 70
6 REPLIES
Junior Contributor

Re: Additional padding characters that are not removable with TRIM function

hex '00' is not a space, only hex '20', you need to trim both.

And the CHR function needs decimal, not hexadecimal values.

You seem to run TD14, try RTRIM(COL1, '2000'XC)

Enthusiast

Re: Additional padding characters that are not removable with TRIM function

We are working on TD 14.10.

When i run the below statement, i get the value as 70 & 10:

select char_length('FILGRASTIM                                                            '), char_length(RTRIM('FILGRASTIM                                                            '));

But when i run the same as below from my table, I got both the lengths as 70 only:

SELECT

    COL1,

    char_length(COL1),

    char_length(rtrim(COL1)) as COL1_TRIM_LEN

FROM DBNAME.TABLENAME

WHERE

char_length(COL1) GT 60;

And when i run the below query I got error: SELECT Failed. 3707:  Syntax error, expected something like ')' between the word 'COL1' and ','.

SELECT

    COL1,

    char_length(COL1),

    char_length(rtrim(COL1,'2000'XC)) as COL1_TRIM_LEN

FROM DBNAME.TABLENAME

WHERE

char_length(COL1) GT 60;

Need further help.

Junior Contributor

Re: Additional padding characters that are not removable with TRIM function

rtrim(COL1,'2000'XC) is valid syntax in TD14.10, there must be some other problem.

Enthusiast

Re: Additional padding characters that are not removable with TRIM function

Thanks for your valuable suggestion. Now this worked when i ran the below query using BTEQ and using Sql Assistant with Teradata .Net provider.

SELECT

    COL1,

    char_length(COL1),

    char_length(rtrim(COL1,'2000'XC)) as COL1_TRIM_LEN

FROM DBNAME.TABLENAME

WHERE

char_length(COL1) GT 60;

3707 Failure occured when  i ran the the same query on SQL Assistant with ODBC Connectivity provider.

What could be the reason for this difference in the run between Teradata .Net and ODBC Connectivity. Please help.

Enthusiast

Re: Additional padding characters that are not removable with TRIM function

Need some help on this...Any suggestions on why RTRIM(COL1,'2000'XC) was not working on SQL Assistant with ODBC connectivity.

Re: Additional padding characters that are not removable with TRIM function

Probabily you have to check the "Allow use of ODBC SQL Extensions in queries", but is better if it is uncheck so you can run with real TERADATA function.

Hope this help