I have a SQL Server 2008 table and a specific column Title which contains a mixture of strings that all start with 'WM' and end in a store number, for example WM24 or WM1234.
What I'd like to do is normalize all the rows to a standard format of WMXXXX' (where XXXX is a 4-digit store number). So WM26 would become WM0026 and WM123 would become WM0123, but WM1234 is unaffected.
Is this doable?
You can use SUBSTRING(Title, 3, LEN(Title) - 2) or RIGHT(Title, Len(Title)-2)) or Replace(Title, 'WM', '') to pull off the numbers from the WM. Then pad it out with zeros.
Here it is with the Substring():
SELECT 'WM' + Right('0000' + Substring(Title, 3, LEN(Title) - 2), 4) as title
FROM yourtable;
Or Right():
SELECT 'WM' + Right('0000' + RIGHT(Title, Len(Title)-2)), 4) as title
FROM yourtable;
Or Replace():
SELECT 'WM' + Right('0000' + Replace(Title, 'WM', '')), 4) as title
FROM yourtable;
Sure this is pretty simple. But I have to ask, if every single row starts with "WM" why bother? That is incredibly redundant. Start by removing that prefix then make a 4 character string of what is left.
select 'WM' + right('0000' + substring(YourColumn, 3, len(YourColumn)), 4)
from YourTable
