Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
add comment

1 Answer

up vote 3 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
add comment

Your Answer

 
discard

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.