Replacing only the nth instance of character

Question

Is there a way a can replace the 1st instance of a character in a string with something eg.

^1402 WSN NIAMLAB^teertS htimS 005

to be

&1402 WSN NIAMLAB^teertS htimS 005

keeping the second ^ in place

Answer 1

To replace the first instance of a character I would recommend the use of the STUFF and CHARINDEX functions. STUFF inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.

DECLARE @str varchar(100) = '^1402 WSN NIAMLAB^teertS htimS 005'
SELECT STUFF(@str, CHARINDEX('^', @str), 1, '&')

Note that you could also use STUFF in a query as follows:

SELECT STUFF(<yourcolumn>, CHARINDEX('^', <yourcolumn>), 1, '&')
FROM <yourtable>