Replace occurrences of characters in a string

Question

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.

Answer 1

As commented by Squirrel, use patindex to find the first and second **'s and then replace to remove any remaining 8s.

select a.TestValue, replace(e.NewString,'*',' ')
from (
  values ('**Ang-Stl-A36-6X4X.5**Angle,A36*,4X6X1/2****')
) a (TestValue)
cross apply (
  values (patindex('%**%', a.TestValue))
) b (FirstPat)
cross apply (
  values (
    case
    when b.FirstPat > 0 then substring(a.TestValue, 1, b.FirstPat - 1) + 'Xx_' + substring (a.TestValue, b.FirstPat + 2, len(a.TestValue))
    else a.TestValue
    end
  )
) c (NewString)
cross apply (
  values (patindex('%**%', c.NewString))
) d (SecondPat)
cross apply (
  values (
    case
    when d.SecondPat > 0 then substring(c.NewString, 1, d.SecondPat - 1) + 'a_' + substring (c.NewString, d.SecondPat + 2, len(c.NewString)) 
    else c.NewString
    end
  )
) e (NewString);

Returns:

TestValue	NewValue
Ang-Stl-A36-6X4X.5Angle,A36*,4X6X1/2****	Xx_Ang-Stl-A36-6X4X.5a_Angle,A36 ,4X6X1/2

DBFiddle