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.

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.

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.

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.

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.

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.

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:

      “`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.

      Like

  2. way0utwest says:

    That’s a great point. Worth another post.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.