Replacing an Unknown Value with SQL Replace

I'm probably missing something really obvious here, but this has been a bear to search for on Google (Maybe I don't have the right terminology).

I want to replace an unknown value with another value from a temp table. I know the length of the value so my thought was to use underscores as you w开发者_如何学JAVAould in a LIKE statement. The following DOES NOT work however:

UPDATE MyTable 
SET Name = 
    Replace(Name, '__SomeString', TempTable.value + ' SomeString')
FROM MyTable INNER JOIN TempTable 
    ON Name LIKE TempTable.Name

This is MS SQL 2000 FWIW.

EDIT: To try and clarify it looks like the underscore '_' wildcard that is used in a LIKE statement is taken literally inside of the replace function. Is there another way? Any thoughts?

---

UPDATE MyTable 
SET Name = 
CASE WHEN (Name like '_SomeString') 
     THEN TempTable.value + SUBSTRING(Name,2,LEN(Name)-1)
     ELSE Name END
FROM MyTable INNER JOIN TempTable     
ON MyTable.Name = TempTable.Name
WHERE MyTable.Name = 'TheNameToReplace' -- I don't know if it will be for a specific name hence the where...

This will then replace 'SomeString' in the Name field, with the value from TempTable.value

Is this what you were looking for or something else?

---

Perhaps you can use stuff instead of replace. You need to know the start position in the string where you want to replace the characters and you need to know the length of the expression that is to be replaced. If you don't know that perhaps you can use charindex or patindex to figure that out.

select stuff('A123', 1, 1, 'B ')

Result:

(No column name)
B 123

---

Would somethi8ng like this work?

UPDATE mytable
SET field1 = 'A' +  SUBSTRING(field1,2,LEN(field1))
WHERE LEFT(field1) IN (0,1,2,3,4,5,6,7,8,9)

---

Apparently it is not possible to use wild cards in the REPLACE function. This is the closest match on SO that I could find: MySQL Search & Replace with WILDCARDS - Query While the link is for MySQL I believe it is true for MS SQL as well.

The other answers here are all creative solutions to the problem, but I ended up going the brute force route.