0

I am splitting a column based on the colon using this code which works.

    ,CASE WHEN CHARINDEX (':', ref) > 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...

3

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) > 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) > 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<>fiddle.

| improve this answer | |
  • Mate ...thank you, I have been staring at this for hours. Really helpful answers! – Stockburn May 7 at 5:34
  • Note that EndVal will be a string type, not numeric, after this. – Michael Green May 7 at 12:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.