Adding spaces to the string

pvsbandi
pvsbandi used Ask the Experts™
on
Hi,

  I have a string field with values as long as 50 characters and as small as 2 characters or some times, a blank value.
  But i need to display this field so that it displays 10 characters only.
 But if it is less than 10 characters, then the first space characters should be appended to the actual field value.
Example : Field = 'ABCDEFGHIJKL'.
Then it should be substr(Field,1,10) which gives me 'ABCDEFGHIJ'.
But if Field = 'ABCD'
Then it should display '      ABCD' ( 6 spaces followed by the field value).
If the field has a null value, the it should still maintain those 10 spaces.

Please help.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
try

select  left ( '           ' || coalesce(your_column, ''), 10)
from your_table
sorry
i ment

try

select  right ( '           ' || coalesce(your_column, ''), 10)
from your_table
Data Warehouse / Database Architect
Commented:

I think that you're going to have to special case this.  

SELECT case when length (string) >= 10 then left (string, 10)
                      else left ('          ', 10 - length (string)) || coalesce (string, '')
             end


Kent

Author

Commented:
You guys rock!
Kent OlsenData Warehouse / Database Architect

Commented:

ohoh...

Upon further review.....

Momi's simpler query for the right justified string is a better solution than what I posted.  Sorry.


SELECT case when length (string) >= 10 then left (string, 10)
                      else right ('          ', coalesce (string, ''))
             end


Kent

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial