Updating part of a row in an MS SQL column

Question

I am looking for a smart idea to update only part of a row in a column. There are many similar questions, but I have not found a way for me.

For example, I have a column varchar(10)

1234|5|6789X
____________
2134|1|71891

How can I easily update the fifth element of my row without touching the rest of the row, there is no template.

I tried to use patindexandsubstring

Answer 1

use

STUFF(Column_Name,Char_Index_Start_Position,Lenght_to_Replace,Replaced_string)

try for your case

STUFF(column_name,5,1,'1')

STUFF ()

Answer 2

How about the material

SELECT Stuff('1234|5|6789X', 6, 1, '1') 

Answer 3

varchar(10), , , ,

1234|5|6789X
____________
2134|1|71891

|5| |1| .

, STUFF

SELECT CASE WHEN LEN (col) >4 THEN STUFF(col,5,1,'X')
ELSE col END FROM tbl

where tblis the name of your table, and colis your column name and replace 'X'with what you want to replace.

Explanation: The reason for use CASEis that if STUFFit encounters a string that does not have a 5th character in the above statement, it returns NULL.