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