Informix

 View Only
  • 1.  How to preserve whitespace at the end of a CHAR or VARCHAR

    Posted 28 days ago
    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
    ------------------------------


  • 2.  RE: How to preserve whitespace at the end of a CHAR or VARCHAR

    IBM Champion
    Posted 28 days ago
    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
    ------------------------------



  • 3.  RE: How to preserve whitespace at the end of a CHAR or VARCHAR

    Posted 28 days ago
    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
    ------------------------------



  • 4.  RE: How to preserve whitespace at the end of a CHAR or VARCHAR

    Posted 28 days ago
    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
    ------------------------------



  • 5.  RE: How to preserve whitespace at the end of a CHAR or VARCHAR

    IBM Champion
    Posted 28 days ago
    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
    ------------------------------



  • 6.  RE: How to preserve whitespace at the end of a CHAR or VARCHAR

    Posted 27 days ago
    Edited by Mark Collins 27 days ago
    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
    ------------------------------