Add Leading Zeros in CASE, CHARINDEX and SUBSTRING Query I am splitting a column based on the colon using this code which works.
    ,CASE WHEN CHARINDEX (':', ref) &gt; 0
THEN SUBSTRING(ref, 1, CHARINDEX(':', ref) -1) 
    ELSE NULL
END AS EndVal

The 'EndVal' is numeric and when it is less than 4 characters long I need to add leading Zeros so

123  = 0123

1    = 0001

1234 = 1234
I know I can accomplish that using the RIGHT or REPLICATE function but I cannot get my head round how I either add it to my existing code or edit it so it does both jobs?


Any advice on the original code also greatly appreciated. Having to get this to work on SQL 2008, yes I want to upgrade but...
You can use RIGHT either inside your CASE statement or outside it, depending on whether you want to retain the NULL value when ':' isn't found in the string.

Inside CASE

SELECT 
  CASE
    WHEN CHARINDEX (':', Ref) &gt; 0 THEN RIGHT('0000' + (SUBSTRING(Ref, 1, CHARINDEX(':', Ref) -1)), 4) 
    ELSE NULL
  END
FROM Data


Outside CASE

SELECT RIGHT('0000' + ISNULL(
  CASE
    WHEN CHARINDEX (':', Ref) &gt; 0 THEN (SUBSTRING(Ref, 1, CHARINDEX(':', Ref) -1))
    ELSE NULL
  END, ''), 4)
FROM Data


The difference here is that NULL values from the CASE statement are replaced with four zeroes in the Outside CASE example, but in the Inside CASE example they remain NULL. You can see the different results in this db&lt;>fiddle.