-1

I have a table that has a column that is an nvarchar. I would like to update that column for any rows that currently have a value and only if their value is less than 1,000.

Pseudo code:

UPDATE TABLE_NAME
IF (PARSE(TABLE_NAME.COLUMN) < 1000)
    SET TABLE_NAME.COLUMN = ADD-LEADING-ZEROS(TABLE_NAME.COLUMN)
ELSE
    do nothing

So basically, if the column contains the value "123" I would like to update it to be "0123". If it's "12" I would like to update it to be "0012", etc.

I'm aware of how to pad leading 0s but I'm not sure how to incorporate it into an update statement since I am still pretty new to sql.

Quiver
  • 1,351
  • 6
  • 33
  • 56
  • 1
    https://stackoverflow.com/a/16760967/656243 – Lynn Crumbling Jun 25 '18 at 20:46
  • https://stackoverflow.com/questions/34093816/add-leading-zeros-to-a-varchar-field – Alex Artsikhovsky Jun 25 '18 at 20:46
  • This is called padding. Googling 'pad t-sql' shows a ton of results. – Lynn Crumbling Jun 25 '18 at 20:46
  • 1
    Possible duplicate of [Pad a string with leading zeros so it's 3 characters long in SQL Server 2008](https://stackoverflow.com/questions/16760900/pad-a-string-with-leading-zeros-so-its-3-characters-long-in-sql-server-2008) – Lynn Crumbling Jun 25 '18 at 20:47
  • @LynnCrumbling Cool, thanks. And as I mentioned in my post, I was aware of the different ways to accomplish "padding" 0s but I wasn't sure how to incorporate it into an update statement. – Quiver Jun 25 '18 at 20:51

1 Answers1

2

You can use LEN for this:

UPDATE dbo.YourTable
SET Column = RIGHT('0000'+Column,4)
WHERE LEN(Column) < 4
;
Lamak
  • 69,480
  • 12
  • 108
  • 116
  • This is exactly what I was looking for. Thanks! I'll accept the answer once the time expires and SO allows me to. – Quiver Jun 25 '18 at 20:49