Post reply

How to update a field with leading zeros

  • October 24, 2019 at 9:00 am

    #3691531

    Hi,

    I have 2 columns in a table:

    rct_ref_to_date   int

    rec_ref                 nchar(10)

    I need to UPDATE rec_ref with the value in rct_ref_to_date but with leading zeros to pad it out to 7 characters.

    Can anybody help me with this, please? I am a beginner so not sure if I am using the correct data type for rec_ref.

    Thanks in advance

     

    • This topic was modified 4 years, 6 months ago by  dace.

  • October 24, 2019 at 9:22 am

    #3691533

    It's nchar(10), so why only seven?

    Anyway:

    UPDATE tbl
    SET    rec_ref = replace(str(rec_ref_to_date, 7), ' ', '0')

    The str function returns a number right-justified padded by spaces. Default length is 10, you can override as in this example.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • October 24, 2019 at 9:25 am

    #3691534

    Hi,

    It's nchar(10) as I need to concatenate 3 text characters also i.e. WEB0000001.

    Thanks for your help

  • October 24, 2019 at 9:56 am

    #3691542

    You can't put leading zeros on a numerical datatype, but you can on a (n)varchar. One method is using RIGHT:

    RIGHT(REPLICATE('0',7) + CONVERT(varchar(7),YourNumericalColumn),7)

    This assumes all values are positive.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • October 24, 2019 at 10:26 am

    #3691546

    Thanks all for your help. Am sorted now.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply