Pad single digit numbers in column with leading zero-CodePudding

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

CodePudding user response：

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;

CodePudding user response：

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;

CodePudding user response：

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