Pad single digit numbers in column with leading zero

user_4408559 0
Viewed 345

I have a requirement to pad any single digit numbers in a string field with a leading zero. I only need to pad single numeric characters with a leading zero, not string alphabetic characters (A-Z). Below is an example of the current data, along with the expected output for these examples.

Current output     Expected output:
9                  09    
19                 19   
15                 15
F                  F
UR                 UR           
B                  B  
0                  00    
4                  04
N                  N
00                 00
sql-server-2014 sql-server sql
3 Answers

Use a CASE expression to check whether the column contains only 0 to 9. If true then prefix with 0 else as it is.

Query

select 
  case when col like '[0-9]' 
    then '0' + col
  else col end as newcol  
from tablename;
user_22949780

Case of course is fine if you need to do it once. But if you do not want to update the Data itself and it just for reading or export use IIF. Here a small example:

declare @test table (
num nvarchar(10)
)

insert into @test
values ('9'),
       ('19'),
       ('15'),
       ('F'),
       ('UR'),
       ('B'),
       ('0'),
       ('00')

update @test
   set num = IIF(num like ('[0-9]'),'0' + num,num)

select *
  from @testenter code here
user_63346930

You could use TRY_CONVERT to NULL the non integer values, and then use concatenation and RIGHT to add the leading zeroes:

SELECT ISNULL(RIGHT('00' + CONVERT(varchar(2),TRY_CONVERT(int,YourColumn)),2),YourColumn)
FROM dbo.YourTable;
user_20299830
Related