Add Leading Zeros in CASE, CHARINDEX and SUBSTRING Query
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.