Add zeros to front of column

Question

I have a column that is a maximum size of 9 characters and I want to do a query that will look at all rows which are less than 9. This:

SELECT *
FROM tEmpCourseAssoc
WHERE ({ fn LENGTH(EmployeeID) } < 9)

Now I want to add zeros to the front of the column EmployeeID if it's less than 9 characters so it adds up to nine characters. For example if the column EmployeeID is 1234567 it would get updated to 001234567 and if it's 12345678 it would get updated to 012345678

I think it's something like:

SELECT RIGHT('0000000' + CAST(myField AS VARCHAR), 9)

But I'm not sure how to implement this. I plan on excuting the query straight from Enterprise Manager. The column EmployeeID is type varchar(10)

http://stackoverflow.com/questions/9520661/formatting-numbers-by-padding-with-leading-zeros-in-sql-server — TGH, Jul 07 '14 at 17:51
you don't need to check the length in your where statement, just pad and then take the Nth many characaters — Limey, Jul 07 '14 at 19:44

score 1 · Accepted Answer · answered Jul 07 '14 at 17:59

1

You can try this

UPDATE  tEmpCourseAssoc SET
        EmployeeID= RIGHT(REPLICATE('0',9) + EmployeeID, 9)
WHERE   LEN(EmployeeID) < 9

answered Jul 07 '14 at 17:59

Jesuraja

3,774
4
24
48

score 0 · Answer 2 · answered Jul 07 '14 at 17:59

0

SELECT     RIGHT(REPLICATE('0', 9) + EmployeeID, 9) AS Expr1
FROM         tEmpCourseAssoc
WHERE     ({ fn LENGTH(EmployeeID) } < 9)

answered Jul 07 '14 at 17:59

user1431633

658
2
15
34

score 0 · Answer 3 · answered Jul 07 '14 at 18:03

0

SELECT     RIGHT(REPLICATE('0', 9) + CAST(EmployeeID AS varchar), 9) AS Res
FROM         tEmpCourseAssoc
WHERE     ({ fn LENGTH(EmployeeID) } < 9)

SQL FIDDLE DEMO

answered Jul 07 '14 at 18:03

fortune

3,361
1
20
30

Could you elaborate on your answer a bit? – Qix - MONICA WAS MISTREATED Jul 07 '14 at 20:57

Add zeros to front of column

3 Answers3