replacing the 8th character in a varchar string

[RJSF]

Hi,

I have problem, where I need to replace the 8th character in a varchar string eg 002556-108823-AN-02 ie I need to change the 1 to 3. (and 2s to 4s in other strings eg 040598-20066-AN-02)

I rarely use SQL but I know I'll need the update and replace function but I don't know how to specify the specific character, especially as there maybe other characters of the same type in the string, but I only need the eighth one in the string changed

Any help would be hughly appreciated

[code green]

Wrong answer given to post

[Delerna]

From Help documents.

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