Changing Values in T-SQL–#SQLNewBlogger

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:

   , myval VARCHAR(100));

INSERT dbo.mytable
     (mykey, myval)
     (1, 'apple,pear,banana')
   , (2, 'pear,peach,melon');

SELECT * FROM dbo.mytable AS m;

This has a few rows of multiple values in a field.

2021-01-04 12_09_31-SQLQuery17.sql - ARISTOTLE.sandbox (ARISTOTLE_Steve (84))_ - Microsoft SQL Serve

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:

    , 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.

2021-01-04 12_17_05-SQLQuery17.sql - ARISTOTLE.sandbox (ARISTOTLE_Steve (84))_ - Microsoft SQL Serve

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.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.

3 Responses to Changing Values in T-SQL–#SQLNewBlogger

  1. brianary says:

    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,’))

    Liked 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:

      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.


  2. way0utwest says:

    That’s a great point. Worth another post.


Comments are closed.