Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
share|improve this question
In SQL Server, I get around this by CASTing the output to the appropriate max length. – JNK Jun 13 '12 at 15:37
up vote 4 down vote accepted

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.

share|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
@MariusButuc Just self-accept. I was guessing since I don't use DB2! – JNK Jun 13 '12 at 16:43

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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