Professional Web Applications Themes

How to add leading zeroes to a numeric field? - Microsoft SQL / MS SQL Server

I want to pad a number with leading zeroes to append it with other characters. I am using sequence numbers. so if the sequence number is 17, it should display as C00017 instead of C17. How can I do so? Thank you, Alice...

  1. #1

    Default How to add leading zeroes to a numeric field?

    I want to pad a number with leading zeroes to append
    it with other characters. I am using sequence numbers.
    so if the sequence number is 17, it should display
    as C00017 instead of C17. How can I do so?

    Thank you,
    Alice
    Alice Guest

  2. #2

    Default Re: How to add leading zeroes to a numeric field?

    Hello Alice !

    Try this:

    select 'C' + RIGHT("00000"+convert(varchar, NAME_OF_COLUMN), 5)


    Jens Süßmeyer


    "Alice" <com> schrieb im Newsbeitrag
    news:02ef01c365cd$c422c560$gbl... 


    Jens Guest

  3. #3

    Default Re: How to add leading zeroes to a numeric field?

    [posted and mailed, please reply in news]

    Alice (com) writes: 

    SELECT 'C' + replace(str(val, 5), ' ', '0')

    For further details, please read in Books Online about the various
    string functions.

    --
    Erland Sommarskog, SQL Server MVP, se

    Books Online for SQL Server SP3 at
    http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
    Erland Guest

  4. #4

    Default Re: How to add leading zeroes to a numeric field?

    Thank you Jens,

    This works.
    -Alice
     
    NAME_OF_COLUMN), 5) 
    >
    >
    >.
    >[/ref]
    Alice Guest

Similar Threads

  1. #40020 [NEW]: int values with leading zeroes
    By pav at oook dot cz in forum PHP Bugs
    Replies: 1
    Last Post: January 4th, 01:24 PM
  2. #40020 [Opn->Bgs]: int values with leading zeroes
    By tony2001@php.net in forum PHP Bugs
    Replies: 0
    Last Post: January 4th, 01:22 PM
  3. Preserving leading zeroes in a query
    By D. Miller in forum Coldfusion Database Access
    Replies: 2
    Last Post: December 8th, 01:41 PM
  4. Leading zeroes
    By Olivier Wirz in forum PERL Beginners
    Replies: 3
    Last Post: January 10th, 09:35 PM
  5. leading zeroes in day, month
    By Mitja Udovc in forum Informix
    Replies: 10
    Last Post: November 4th, 10:09 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139