Informix

 View Only

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

    Posted Tue July 19, 2022 11:33 AM
    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


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

    IBM Champion
    Posted Tue July 19, 2022 12:04 PM
    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
    ------------------------------

    Original Message


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

    Posted Tue July 19, 2022 12:20 PM
    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
    ------------------------------

    Original Message


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

    Posted Tue July 19, 2022 06:26 PM
    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
    ------------------------------

    Original Message


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

    IBM Champion
    Posted Tue July 19, 2022 08:56 PM
    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
    ------------------------------

    Original Message


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

    Posted Wed July 20, 2022 09:44 AM
    Edited by System Fri January 20, 2023 04:28 PM
    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
    ------------------------------

    Original Message