CODEWITHSUNDEEP
sqlsql-server
Ian Vink
Ian Vink

Reputation: 68750

Adding a padding 0 to a SQL column

With SQL Server 2014, I have a nvarchar(10) column with values like this

9998
03345
05567
6675

I need to add a left padded "0" to all the cells that are 4 digits long so that all values are 5 digits.

How do I write an update that will take into account the width of the current value?

update MyTable set MyField= RIGHT('00000' + ?????, 5)

Upvotes: 1

Views: 1379

Answers (1)

Felix Pamittan
Felix Pamittan

Reputation: 31879

You can use RIGHT:

SELECT RIGHT('00000' + <string>, 5)

Your UPDATE statement would look like this:

UPDATE <Table>
    SET <column> = RIGHT('00000' + <column>, 5)

You could add a WHERE clause to filter all rows with LEN < 5 

WHERE LEN(<column>) < 5

Upvotes: 4

Related Questions