4

I am looking for smart idea to update just a part of string in column. There is lot of similar question but I haven't found way for me.

For example I have column varchar(10)

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

How I can easy update 5th element of my string without touching rest of string, there is no pattern.

I was trying use patindex & substring

Improve this question
3
  • 1
    Show us your tries with pathindex and substring Commented Sep 23, 2015 at 10:01
  • Do pipes occures always?? Commented Sep 23, 2015 at 10:03
  • How I can easy update 5th element of my string what is the 5th element? Can you show what you have tried and what is the expected output? Commented Sep 23, 2015 at 10:05

3 Answers 3

Reset to default
4

use

STUFF(Column_Name,Char_Index_Start_Position,Lenght_to_Replace,Replaced_string)

for your case try

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

STUFF()

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

How about Stuff

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

Comments

0

Since your column is varchar(10), I assume that in the data you have give us in question

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

|5| and |1| need to be replaced.

As many have pointed out already, you can use STUFF

However correct way to implement it would be 

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

where tbl is your table name and col is your column name and replace 'X' with what you want as replacement.

Explanation: Reason for using CASE is because if STUFF encounters a string which does not have 5th character in above statement then it returns NULL.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.