In a table, I have an ID column defined as BIGINT(8) and one VOUCHER column that is VARCHAR(100). No matter the actual length of the value in that field, the SQL always returns a full length value, which can be cumbersome in CLI, specially when selecting more fields.

Here is a sample of what I'm getting

ID                   VOUCHER                                                                                               
-------------------- ----------------------------------------------------------------------------------------------------                                                                   
               26462 7083d8374831148f9e2271741529ee37                                                                                                                                                 
               26624 9170220e6971c2414613d0a69b420156                                                                    
               26625 b75d12847a24981d36f7bfcd58e8a230  

I tried TRIM / LTRIM / RTRIM functions, but without success.

What can I use to get something along these lines, at least for display purposes:

ID     VOUCHER                           
------ --------------------------------
26462  7083d8374831148f9e2271741529ee37
26464  9170220e6971c2414613d0a69b420156
26465  b75d12847a24981d36f7bfcd58e8a230
  • In SQL Server, I get around this by CASTing the output to the appropriate max length. – JNK Jun 13 '12 at 15:37

Thanks to @JNK's comment, select cast(trim(id) as varchar(6)) as id, cast(trim(voucher) as varchar(32)) as voucher, auth from my_table does the job.

| improve this answer | |
  • Now how do I accept @JNK's suggestion? – Marius Butuc Jun 13 '12 at 15:52
  • He has to convert his comment to an answer. – Nick Chammas Jun 13 '12 at 15:54
  • 1
    @MariusButuc Just self-accept. I was guessing since I don't use DB2! – JNK Jun 13 '12 at 16:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.