unwanted space is coming in resulted export file

General
Enthusiast

unwanted space is coming in resulted export file

Hi All,

I have used TRIM ( BOTH FROM string_name) command to remove extra character / space coming in export file but it is not working. could you please help to resolve this issue ?

.set format on;

.set width 1000;

.set pagelength 100000;

.set heading '';

.set footing '';

.set titledashes off;

.set separator 0;

.set null as '';

.export file=result.txt;

sel TRIM(BOTH FROM SPF.columne_1) (TITLE '') ,

TRIM(BOTH FROM SPF.columne_2) (TITLE '') ,

TRIM(BOTH FROM SPF.columne_3) (TITLE '') ,               

TRIM(BOTH FROM SPF.columne_4) (TITLE '') 

TRIM(BOTH FROM DEST_IPV6ADDRESS) (TITLE '')

FROM   databasename.table_name SPF 

Output: 

                                                                              0   XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

                                                                                        YYYYYYYYYYYYYYYYYYYYYYYY

                                                                                              ZZZZZZZZZZZZZZZZZZZZZZZZZZZZ

ZZZZZZZZZZZZZZZZZZZZZZ

coulmn 1 outpu denotes as 0 

coulmn 2 outpu denotes as X 

coulmn 3 outpu denotes as Y 

coulmn 4 outpu denotes as Z

1 REPLY
Enthusiast

Re: unwanted space is coming in resulted export file

usually the export results in a char format. Hence takes the  length of the column and replaces with blanks. Instead, try casting all the columns to varchar(max(length of that column)). Also to reduce the size of the file and get rid of some junk characters cast the whole output of all the five columns to char(sum(max(length(all columns))).

HTH