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.
Scenario
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.
Solution
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.
SQLNewBlogger
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.
Voice of the DBA 
This could quickly get complicated if you replace “apple”, but “pineapple” is a possible value. If you are lucky enough to have the latest T-SQL, you could add commas to the ends and replace the term including the commas.
TRIM(‘,’ FROM REPLACE(‘,’+myVal+’,’, ‘,apple,’, ‘,grape,’))
LikeLiked by 1 person
Excellent point. And, fortunately you can handle this easily enough in any version of SQL Server (at least back to 2000) via a simple `SUBSTRING`. The following shows the `TRIM` and `SUBSTRING` producing the same result:
“`sql
DECLARE @myVal NVARCHAR(50) = ‘apple,pear,pineapple’;
SET @myVal = REPLACE(‘,’+@myVal+’,’, ‘,apple,’, ‘,grape,’);
SELECT @myVal,
TRIM(‘,’ FROM REPLACE(‘,’+@myVal+’,’, ‘,apple,’, ‘,grape,’)),
SUBSTRING(@myVal, 2, LEN(@myVal) – 2);
“`
And both options seem to work equally well when @myVal is either NULL or an empty string. Only difference I can think of is that if, for some odd reason, @myVal was allowed to contain empty array elements such that the incoming string began and/or ended with one or more commas, then the `TRIM` would also remove those. But, there aren’t many scenarios where people are allowing for empty entries.
LikeLike
That’s a great point. Worth another post.
LikeLike