How to update a part of the column data using replace function in SQL?

Question

I am trying to update a table coulmn, the table has thousands of records.

Currently I am updating the table by running the following query manually for some of the records.

UPDATE MyTable
SET column = REPLACE(column, 'ABC', 'ABC9')
WHERE where   column like ‘ABC%’

Now I am trying to generate a generic query to update the table by adding a letter '9' after the alphabets. Thanks for your help

Pரதீப் · Accepted Answer · 2016-02-23 15:33:09Z

7

Patindex - Helps you to identify the first occurrence of numeric character in the string

Stuff - Helps you to insert 9 before the first occurrence of numeric character in the string

UPDATE MyTable
SET column = stuff(column,patindex('%[0-9]%',column),0,'9')

answered Feb 23, 2016 at 15:30

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

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

2 Comments

You should also explain what it does, But +1 for the solution

@sagi - Thanks :) updated with MSDN links and some explanation