How to preserve whitespace at the end of a CHAR or VARCHAR
Gustavo Echenique lost trailing spaces in the last column of a fixed-width file. Art Kagel said UNLOAD strips trailing spaces from CHAR columns and suggested VARCHAR or a cast; RPAD(...)::varchar(50) was suggested after the plain RPAD attempt failed. Mark Collins suggested an external table with FORMAT FIXED, untested. No confirmation of a fix.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Dear colleagues: I am building a flat file or text file with 34 columns, each with a predefined format, and I find that the blank spaces in the last column do not appear. I have defined the 34 columns as CHAR with the length that corresponds to each one, and as I told you, the blank spaces in column 34 do not appear. It should be clarified that I use the LPAD and RPAD functions as appropriate to a numeric or alphanumeric data . For a test, instead of declaring this last column with CHAR, I declared it as VARCHAR but it doesn't work anyway. What surprises me is that if I add any other character (I used the hyphen '-' ), it shows it. And this happens only if I declare the column as VARCHAR. I hope some of you can help me. Thank you very much in advance for your attention. ------------------------------ Gustavo Echenique ------------------------------ #Informix
Trailing spaces are stripped from CHAR type columns when you unload to save space and speed up the unload. Change those columns to VARCHAR or LVARCHAR or cast them in the SELECT to include the explicit spaces. ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------
Thank you very much Art! I'm going to try casting them in the SELECT, since using VARCHAR doesn't work for me. A hug! ------------------------------ Gustavo Echenique ------------------------------
Hi Art! I have tried with VARCHAR and RPAD inside the SELECT, but there is no way to fill the spaces. I add a small example, since the final query is more complex, but it serves as a sample: SELECT RPAD( TRIM( c.Razon_Social), 50, ' ' ) Razon_Social It always returns the exact number of characters stored in the column "Razon_Social" ------------------------------ Gustavo Echenique ------------------------------
Try it this way. SELECT RPAD( TRIM( c.Razon_Social), 50, ' ' )::varchar(50) Razon_Social Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------
It sounds like you're using the UNLOAD statement. Have you tried using an external table with FORMAT FIXED? It is a little more cumbersome than an unload, but you should be able to define the columns as a fixed size. Depending on what you're doing with the flat file after it's created, you may need to add columns to add delimiter characters:
CREATE EXTERNAL TABLE my_ext_table
( column_a char(34)
, delimiter_1 char(1)
, column_b char(34)
, delimiter_2 char(1)
.
.
.
)
USING (FORMAT 'FIXED',
DATAFILES('DISK:/some/random/directory/my_ext_table')
);
INSERT INTO my_ext_table
SELECT column_a, '|', column_b, '|' ... FROM source_table;NOTE: I have not tested this, so some of the syntax may need to be tweaked.
------------------------------
Mark Collins
------------------------------