Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2003
    Posts
    11

    Question Unanswered: Manipulating output

    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.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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

  3. #3
    Join Date
    Aug 2003
    Posts
    11

    Manipulating output

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

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote 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:
    Code:
    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:
    Code:
    SELECT Cast('xyzzy' AS Char(30))
    These can also be combined if you like.

    -PatP

  6. #6
    Join Date
    Aug 2003
    Posts
    11

    Smile Manipulating output

    PatP, thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •