0

I've used the answer at Pad a string with leading zeros so it's 3 characters long in SQL Server 2008

to pad a column with leading zeros in my case it's two zeros and I'm using this:

RIGHT('0'+ISNULL(REPLACE(<columnName>, '"', ''),''),2)

Data ranges from 1 to 99 but also includes a value of of 'UNK' for unknown which is truncated using this method.

Is there a way around this or should I be exploring a different solution as I'm dealing with text and numbers in the same column?

Cheers

J

Community
  • 1
  • 1
james.mullan
  • 149
  • 2
  • 3
  • 16

2 Answers2

2

(Second revision) Try this:

SELECT CASE WHEN LEN(ISNULL(<columnName>, '')) > 1
  THEN ISNULL(<columnName>, '')
  ELSE RIGHT ('00'+ ISNULL(<columnName>, ''), 2)
END

Sample input/output:

Input NULL  / Ouput '00'
Input 'UNK' / Ouput 'UNK'
Input '1'   / Ouput '01'
Input '99'  / Ouput '99'
bastos.sergio
  • 6,684
  • 4
  • 26
  • 36
  • Hi @bastos.sergio, thank you for the answer - is it possible to handle both the UNK and single/double digit values in one statement? e.g. I want to convert 1 to '01' but keep 10 as '10' and UNK as 'UNK'. so, in effect only add a zero to single digit numbers and leave everything else a lone. – james.mullan Mar 20 '14 at 10:20
  • That's perfect, thank you very much for taking the time to answer! – james.mullan Mar 20 '14 at 15:20
0

Regarding comment added below @bastos.sergio's answer:

SELECT CASE isnumeric(Val) AND len(Val) = 1 THEN '0' + Val ELSE Val END
Kapol
  • 6,383
  • 3
  • 21
  • 46