how to convert various center to desired 5 digit format

Question

I have a colum LocationID which is varchar(10) and I need to convert them to NewLocation

Location_ID     NewLocation
123             00123
2233            02233
42345           42345
22              00022
10              00010 

Select (Concat('00'+Location_ID)) as NewLocation
from tablelocation;

My concat statement can not evaluate when to place one zero or two zero in front of location_ID. The goal is to make 5 digit location_ID.

possible duplicate of [Pad a string with leading zeros so its 3 characters long in SQL Server 2008](http://stackoverflow.com/questions/16760900/pad-a-string-with-leading-zeros-so-its-3-characters-long-in-sql-server-2008) — Marco, Mar 05 '15 at 22:05

score 0 · Answer 1 · answered Mar 05 '15 at 22:19

0

SELECT RIGHT('00000' + CAST(Location_ID  AS VARCHAR(5)) , 5)

answered Mar 05 '15 at 22:19

M.Ali

67,945
13
101
127

score 0 · Answer 2 · answered Mar 06 '15 at 10:02

declare @t table (ID INT)
insert into @t(ID)values (123)
insert into @t(ID)values (42345)
insert into @t(ID)values (23)
insert into @t(ID)values (22)
insert into @t(ID)values (10)


DECLARE @Length int
SET @Length = 5


select right( POWER(10, @Length) + ID, @Length) from @t

how to convert various center to desired 5 digit format

2 Answers2