CODEWITHSUNDEEP
sqlsql-servert-sql
graphene
graphene

Reputation: 157

Replace leading zeroes does not work without making explicit the Ids

I was updating a column named PostNumber in a table to put leading zeroes. Its data type is nvarchar(50). It should be 6 digits always. If not six, it should be padded with leading zeroes. I have tried with the following T-SQL:

update table1
set PostNumber=RIGHT('000000'+ISNULL(PostNumber,''),6)

The initial table1 is:

   Id   PostNumber
    1       234
    2      24545
    3     435434

What we need to do:

    Id   PostNumber

    1      000234
    2      024545
    3      435434

Very strangely it does not work with the former T-SQL! Unless we explicitly set the Id..

update table1
set PostNumber=RIGHT('000000'+ISNULL(PostNumber,''),6)
where Id between 1 and 3

The last T-SQL works, but I do not know why the former T-SQL does not work ! The table remains the same without putting any zeroes at all. Could you provide a possible cause? Thanks. I really need a bulk update without making explicit the Ids.

Upvotes: 0

Views: 63

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67321

Your actual issue

If your column PostNumber is of a numeric type, the value 123 will be the same as the value 000123. Leading zeros are a matter of string types or - in most cases something you need only in your presentation layer.

UPDATE Did not read carefully enough...

In a comment you stated, that the column is a nvarchar(50)

It might be, that there are blanks around your values, try this:

set PostNumber=RIGHT('000000'+ISNULL(LTRIM(RTRIM(PostNumber)),''),6)

Check with 

SELECT PostNumber,LEN(PostNumber),DATALENGTH(PostNumber) FROM ...

padding approaches

There are several approaches, I prefer the one with REPLACE on STR

DECLARE @number INT=34;
SELECT REPLACE(STR(@number,6),' ','0')

The problem with the very usual approach with RIGHT is the fact, that it will return bad result in cases were the number exceeds the number of digits.

DECLARE @number6 INT=123456;
SELECT REPLACE(STR(@number6,6),' ','0')
      ,RIGHT('000000'+ISNULL(@number6,''),6);

DECLARE @number7 INT=1234567;
SELECT REPLACE(STR(@number7,6),' ','0')
      ,RIGHT('000000'+ISNULL(@number7,''),6);

To be honest: My favorite approach has a weakness with negativ numbers:

DECLARE @numberMinus INT=-15;
SELECT REPLACE(STR(@numberMinus,6),' ','0')
      ,RIGHT('000000'+ISNULL(@numberMinus,''),6);

Starting with SQL-Server 2012 there is FORMAT()

SELECT FORMAT(@number,'000000')

Not the best perfomer, but works nicely.

Upvotes: 2

Related Questions