Values in the column Name_Orig
look something like this:
**Ang-Stl-A36-6X4X.5**Angle,A36*,4X6X1/2****
I want
- First occurrence of ** to be replaced by Xx_
- Second occurrence of ** to be replaced by a_
- Replace all other * are to be with space
This is not a complete table, yet, these set of condition go in Common Table Expression(CTE)
I can replace the first occurence of ** For the second and the third point the query doesn't work
CASE
When Name_Orig LIKE '**%' THEN 'Xx_'+ SUBSTRING(Name_Orig, 3, LEN(Name_Orig)-2)
WHEN CHARINDEX('**', Name_Orig, CHARINDEX('**', Name_Orig) + 1) > 0 THEN
REPLACE(STUFF(Name_Orig, CHARINDEX('**', Name_Orig, CHARINDEX('**', Name_Orig) + 1), 2, 'a_'), '*', ' ')
ELSE Name_Orig
END
Using SQL Server 2005.
charindex()
to find first 2 occurrence and replace with the required string. for the rest, usereplace()