[kiloez]

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.

[andrewst]

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

[kiloez]

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

[andrewst]

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

[Pat Phelan]

Originally Posted by kiloez

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.

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:

SELECT Replace(Str(599826, 13), ' ', '0')

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:

SELECT Cast('xyzzy' AS Char(30))

These can also be combined if you like.

-PatP

[kiloez]

PatP, thanks.