All Forums General
EUsha 25 posts Joined 03/14
21 Oct 2014
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                                                            

46494C4752415354494D2020202020202020202020202020202020 202020202020202000000000000000000000000000000000000000 00000000000000000000000000000000

70

70

FILGRASTIM                                                            

46494C4752415354494D2020202020202020202020202020202020 202020202020202000000000000000000000000000000000000000 00000000000000000000000000000000

70

70

FILGRASTIM                                                            

46494C4752415354494D2020202020202020202020202020202020 202020202020202000000000000000000000000000000000000000 00000000000000000000000000000000

70

70

 

EUsha
dnoeth 4628 posts Joined 11/04
21 Oct 2014

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)

Dieter

EUsha 25 posts Joined 03/14
22 Oct 2014

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.

EUsha

dnoeth 4628 posts Joined 11/04
22 Oct 2014

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

Dieter

EUsha 25 posts Joined 03/14
24 Oct 2014

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.

EUsha

EUsha 25 posts Joined 03/14
27 Oct 2014

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

EUsha

skywalker69692 2 posts Joined 03/13
14 Dec 2014

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

You must sign in to leave a comment.