What can I do to accomplish the following:

Output 13 character values with leading zeros (599826 output as 0000000599826)

Outputting 30 char values with trailing blanks if necessary (20 char string with 10 char trailing blanks/spaces)

Can this be done in SQL?

Any help would be appreciated.

Which DBMS?

For Oracle use LPAD and RPAD functions, e.g.

SQL> select lpad('599826',13,'0') from dual;

LPAD('599826'
-------------
0000000599826

SQL> select rpad('599826',13,'0') from dual;

RPAD('599826'
-------------
5998260000000

I'm sorry, I'm using SQL Server 2000.

I just took a look at the SQL Server docs and couldn't see equivalent functions - but then I'm no SQL Server expert. However, you could do it with a combination of other functions - something like:

left('0000000000000',13-len(string))||string

The leading zeros are relatively easy as long as you don't have to cope with negative numbers. To zero fill positive numbers on the left, you can use:Dealing with negative numbers is enough more complicated that I recommend a user-defined function to avoid the expression clutter.

To space fill a string on the right, you can use:These can also be combined if you like.

-PatP

PatP, thanks.