CODEWITHSUNDEEP
sqlsql-server
Maria
Maria

Reputation: 51

How can I replace multiple words of a string in SQL

Is it possible to replace multiple words in a string in sql without using multiple replace functions?

For example I have a string where I need to replace word 'POLYESTER' with 'POLY' , 'COTTON' with 'CTN', 'GRAPHIC' with 'GRPHC' etc in order to keep the string length at a max of say 30 without much loosing the readability of contents in it(can't use substring to limit chars since it can trim the end meaningful parts of string completely). So we decided to short some keywords like above.

Current query I have used :

SELECT
    REPLACE(REPLACE('**Some string value **COTTON **Some string value ** POLYESTER', 'POLYESTER', 'POLY'), 'COTTON', 'CTN')

If I have 10 keywords like this, what will be the best way to achieve the result other than using multiple replace function. I am using SQL Server 2012.

Upvotes: 2

Views: 7100

Answers (2)

Bart Hofland
Bart Hofland

Reputation: 3905

The answer of mehmetx is actually very nice.

If you need your replacement functionality on a regular basis, you could think about using a normal table instead of a temporary table.

But if you need this logic only once in a while, and performance is not much of an issue, you could avoid the additional replacements table altogether and use a table expression in the FROM clause instead. Something like this:

DECLARE @someValue AS NVARCHAR(MAX) = '**Some string value **COTTON **Some string value ** POLYESTER';

SELECT @someValue = REPLACE(@someValue, fromText, toText)
FROM
  (VALUES
    ('POLYESTER', 'POLY'),
    ('COTTON', 'CTN'),
    ('GRAPHIC', 'GRPHC')
  ) AS S (fromText, toText);

EDIT:

I noticed, that this logic regrettably does not work as expected when used in an UPDATE statement to update existing data in a table.

For that purpose (if needed), I created a user-defined function that performs the replacement logic. I called it MultiReplace. And it does not use the replacement data from a temporary table, but from a "normal" table, which I called Replacements.

The following code demonstrates it. It uses a data table called MyData, which gets updated with all replacements in the Replacements table using the MultiReplace function:

IF OBJECT_ID('MultiReplace') IS NOT NULL
   DROP FUNCTION MultiReplace;

IF OBJECT_ID('Replacements') IS NOT NULL
   DROP TABLE Replacements;

IF OBJECT_ID('MyData') IS NOT NULL
   DROP TABLE MyData;

GO

CREATE TABLE Replacements (
   fromText VARCHAR(100),
   toText VARCHAR(100)
);

CREATE TABLE MyData (
  SomeValue VARCHAR(MAX)
)

GO

CREATE FUNCTION MultiReplace(@someValue AS VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
  SELECT @someValue = REPLACE(@someValue, fromText, toText) FROM Replacements;
  RETURN @someValue;
END;

GO

INSERT INTO MyData (SomeValue)
  VALUES
    ('**Some string value **COTTON **Some string value ** POLYESTER');

INSERT INTO Replacements (fromText, toText)
  VALUES 
    ('POLYESTER', 'POLY'),
    ('COTTON', 'CTN'),
    ('GRAPHIC', 'GRPHC');

SELECT * FROM MyData;
UPDATE MyData SET SomeValue = dbo.MultiReplace(SomeValue)
SELECT * FROM MyData;

But perhaps using multiple REPLACE statements might be more straightforward after all?...

EDIT 2:

Based on the short conversation in the comments, I could propose a simpler solution that uses multiple REPLACE statements in a clearer way. I have only tested it on SQL Server 2019; I am not sure if it will work correctly on SQL Server 2012.

Again, I use a table called MyData for testing here. But there are no additional database objects anymore.

Regrettably, I did not get it to work with a temporary table containing the replacement values.

-- Preparations:

IF OBJECT_ID('MyData') IS NOT NULL
   DROP TABLE MyData;

CREATE TABLE MyData (
  SomeValue VARCHAR(MAX)
);

INSERT INTO MyData
  VALUES
    ('**Some string value **COTTON **Some string value ** POLYESTER'),
    ('**Another string value **GRAPHIC **Another string value ** POLYESTER');

-- Actual work:

SELECT * FROM MyData; -- Show the state before updating

DECLARE @someValue VARCHAR(MAX);

UPDATE MyData
SET
  @someValue = SomeValue,
  @someValue = REPLACE(@someValue, 'POLYESTER', 'POLY'),
  @someValue = REPLACE(@someValue, 'COTTON', 'CTN'),
  @someValue = REPLACE(@someValue, 'GRAPHIC', 'GRPHC'),
  SomeValue = @someValue;

SELECT * FROM MyData; -- Show the state after updating

Upvotes: 2

mehmetx
mehmetx

Reputation: 860

considering sql server is your only instrument (not a c# or another application), as a workaroud; use a temp or persistent table to store replacement options.

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
   DROP TABLE #tmp

CREATE TABLE #tmp (
   fromText VARCHAR(16),
   toText VARCHAR(16)
);

INSERT INTO #tmp (fromText, toText)
    VALUES 
('POLYESTER', 'POLY'),
('COTTON', 'CTN'),
('GRAPHIC', 'GRPHC')

DECLARE @someValue AS NVARCHAR(MAX) = 
'**Some string value **COTTON **Some string value ** POLYESTER';

SELECT @someValue = REPLACE(@someValue, fromText, toText) FROM #tmp;
PRINT @someValue

and the result is: **Some string value **CTN **Some string value ** POLY.

Upvotes: 8

Related Questions