Results 1 to 7 of 7
  1. #1
    Join Date
    May 2003
    Posts
    4

    Unanswered: adding leading zeros in select

    Hi all,

    I'm trying to select a bigint field and format it with leading zeros. I've tried the convert function but it does not seem to support this basic feature. There is no reference in the document on CONVERT or CAST, it only refers to formatting dates.

    example:
    table contents
    123
    456
    789


    desired select result:
    000123
    000456
    000789

    Who has a solution for this problem?

  2. #2
    Join Date
    Apr 2003
    Location
    Israel
    Posts
    81
    select
    replicate('0', 6-datalength(convert(varchar,NumericField)))
    from MyTable

    Or, better:

    create function LZero(@MyNum int, @MyLength int)
    returns varchar
    as
    begin
    declare @MyStr varchar
    set @MyStr = convert(varchar,@MyNum)
    if @MyLength>datalength(@MyStr)
    set @MyStr = replicate('0',(@MyLength-datalength(@MyStr)))+@MyStr
    return @MyStr
    end
    go
    select dbo.LZero(NumericField,6) from MyTable
    --
    kukuk

  3. #3
    Join Date
    May 2003
    Posts
    4
    Thanx for your reply.

    The first solution you offer works fine and suits my needs. I can't get the second to work properly however. The query only returns '0' for every row.

    But this will get me where I want. Thank you very much

  4. #4
    Join Date
    Apr 2003
    Location
    Israel
    Posts
    81
    Ok.
    I have no SQL Server near me to debug it, so go for the 1st option.
    --
    kukuk

  5. #5
    Join Date
    May 2003
    Posts
    4
    Originally posted by kukuk
    Ok.
    I have no SQL Server near me to debug it, so go for the 1st option.
    OK, I will, thanx again

  6. #6
    Join Date
    Dec 2002
    Location
    Antwerp, Belgium
    Posts
    227
    the reason you only get '0' is that no length for the returning varchar is specified. Change it e.g. to varchar(50) and you will get a correct result.
    Johan

  7. #7
    Join Date
    May 2003
    Posts
    4
    Originally posted by jora
    the reason you only get '0' is that no length for the returning varchar is specified. Change it e.g. to varchar(50) and you will get a correct result.
    Thanx for your reply, I already found it out and fixed it.

Posting Permissions

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