Link to home
Create AccountLog in

asked on

Replace words using variable string

I would like an update statement that modifies all words that start with a given string.
For example update all words that start with 'the' to be 'xyz'

many theories
essentially thematic
the theme of the play
theology is the study of

The update statement would modify column1 so that rows read:

many xyz
essentially xyz
xyz xyz of xyz play
xyz is xyz study of
Avatar of mastoo
Flag of United States of America image

Update Mytable
  Set Column1 = Replace( Column1, 'the', 'xyz')

I'm afraid this cannot be done in pure SQL, since update works on the entire column, not on the single tokens. I thus think that you will have to write a function that gets the column value, splits it into tokens, parses each token and then concatenates the updated tokens again.

Best regards,



can you expand on your comments with some example code?
You can find a function to split a string into a table of tokens here:

Next, update every field in this table (say you named it #tokens, column named token)

update #tokens set token = 'xyz' where token like 'the%'

Finally, concatenate the update tokens to form the output. A sample of that can be found at

The result of the last function is your updated string. However keep in mind that updating a lot of values this way might be time consuming as it's all string operations instead of native SQL instructions.

Best regards,

I've tried to apply the split a string function, but do not have enough understanding.  i can get it to work fine, if the string is written in the select statement, but can't figure out how to apply this function to my exiting table (which has multiple strings)
Avatar of Scott Pletcher
If you accept a loose, but reasonable, definition of "word", you can do this easily with standard SQL.

For example, would you be willing to define a "word" as the first alpha character that occurs after a non-alpha character?  Or the first alpha characters that occurs after a non-alpha and non-numeric character?
Yes, I'm willing to define a "word" as the first set of alphanumeric characters that occur after a space.
For efficiency, do the rows have some type of identity / key value that can be stored to get back to the one row that has a particular column that needs a value(s) modified?
If you want the code to be specific to what you have, I'll need to know what the column name(s) are.

If not, I'll just assume it's name "ID" and write the code that way.
Ok.  Let's use the following example table:

a       many xyz
b       essentially xyz
c       xyz xyz of xyz play
d       xyz is xyz study of
Sorry, once you've defined "word" that tightly, a single simple UPDATE will do:

--add some variables to make it easier to re-use the code
DECLARE @old_string varchar(30)
DECLARE @new_string varchar(30)
SET @old_string = 'the'
SET @new_string = 'xyz'

UPDATE dbo.tablename
SET column1 = SUBSTRING(REPLACE(' ' + column1, ' ' + @old_string, ' ' + @new_string), 2, 8000)
--<<-- 8000 = length = must be at least as long as the longest string
    column1 LIKE @old_string + '%' OR
    column1 LIKE '% ' + @old_string + '%'
Your code only replaces the specific string when found/matched, but I need the entire word to be replaced when the string is found within a word.
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
I apologize, but I didn't realize that you had posted your latest suggestion.  I will take a look at it and see how it goes.