IF THEN UPDATE

Question

I would remove the IF statement but I have data with different lengths that are less than 7 characters that I need to update therefore, i was thinking of also adding ELSE.

I need to bring all values in this field that are less then 7 characters up to 7 characters with leading zero's

Anyone have a better way or can tell me why this wouldn't work?

IF
(SELECT LEN(field) AS Legnth FROM TADIDINFO WHERE LEN(field)) = 2
UPDATE table
SET field = '000000' + field
WHERE LEN(field) = 2
ELSE
(SELECT LEN(field) AS Legnth FROM TADIDINFO WHERE LEN(field)) = 3
UPDATE table
SET field = '0000' + field
WHERE LEN(field) = 3
GO

Answer 1

Hi,

So, would you like to fill up a field with leading zeros in a table, until they all have 7 digits?

Try with this then:

UPDATE DestinationTable
SET Field = ISNULL(REPLICATE('0', 7 - LEN(Field)), '') + Field

If you would like to update only those which have only 2 or 3 digits in the Field column:

UPDATE DestinationTable
SET Field = ISNULL(REPLICATE('0', 7 - LEN(Field)), '') + Field
WHERE LEN(Field) IN (2, 3)

Regards,

Zoli

Answer 2

Update table
Set field = Right('0000000' + field, 7)
Where LEN(field) < 7

---

Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

Answer 3

thank you Zoli

Answer 4

Here is a fast, portble and easy way to pad leading zeroes. 
 
UPDATE Foobar
    SET some_col 
      =  SUBSTRING (REVERSE(REVERSE (some_col + '0000000'), 1, 7);

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL