Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.
Recently I ran across a question posted by a beginner on the Internet and thought this would be a good, basic topic to cover. The question was: how can I replace a value in a comma separated string in a table?
This post covers the basics of this task.
Suppose you have some strings in a table, and they contain multiple values. I see this often when application developers serialize some data. For example, I might create a table like this:
CREATE TABLE mytable ( mykey INT NOT NULL CONSTRAINT mytablepk PRIMARY KEY , myval VARCHAR(100)); GO INSERT dbo.mytable (mykey, myval) VALUES (1, 'apple,pear,banana') , (2, 'pear,peach,melon'); GO SELECT * FROM dbo.mytable AS m;
This has a few rows of multiple values in a field.
Imagine now I need to change pear to grape in all rows. I want a simple solution to do this.
I have seen some people try to use complex substring calls paired with other functions to do this, but T-SQL gives you a really simple solution. We have a REPLACE() function that allows us to change a string without parsing it.
The simple way to do this is like this:
SELECT m.mykey , m.myval , REPLACE(m.myval, 'pear', 'grape') AS newstring FROM dbo.mytable AS m;
Always run a SELECT before an UPDATE, but in this case, I can see that pear has been removed and grape is in its place.
REPLACE() works by passing in a string as the first parameter, then a second string to search for, pear in this case, and finally a replacement. I could then put together an UPDATE statement to change my table.
UPDATE dbo.mytable SET myval = REPLACE(myval, 'pear', 'grape') FROM dbo.mytable AS m;
If I run this, the results shown above for newstring will replace the myval string for all rows.
This is an example of a basic type of T-SQL solution that is simple, with a quick explanation. I answered this for someone and then spent 10 minutes writing this up.
A good story to have ready for an interview.