From Help documents.
Quote:
REPLACE
Replaces all occurrences of the second given string expression in the first string expression with a third expression.
Examples
This example replaces the string cde in abcdefghi with xxx.
SELECT REPLACE('abcdefghicde','cde','xxx')
Replace dosn't work with character position.
If you must replace a character at a particular position in a string then I don't know of any SQL fuction that specifically does that. If someone does know then I for 1 am interested.
Here is my clunky method to replace the 8'th character of a string in a query
-
select left(str,7) + 'Replacement char' + right(str,len(str)-8) as str
-
In the particular example that you provide then multiplying the 8th character by 2 will change the 1 to a 2 and the 2 to a 4
so
-
select left(str,7) + convert(char(1),convert(int,substring(str,8,1))*2) + right(str,len(str)-8) as str
-
If not always 1 or 2 then you may need to do successive runs of your update query. Once for each number in the 8th position.
Or use CASE WHEN to make decisions based on the value of the 8th character
You can always make a UDF
-
CREATE FUNCTION fnReplaceCharAtPos(@Str varchar(8000),@Pos int, @Chr char(1))
-
RETURNS varchar(8000) AS
-
BEGIN
-
declare @Res varchar(8000)
-
set @Res=left(@Str,@Pos-1) + @Chr + right(@Str,len(@Str)-@Pos)
-
return @Res
-
END
-
and call it in a query with
-
select dbo.fnReplaceCharAtPos(str,8,'Z')
-
from YourTable
-
