sql server - How can I replace multiple words of a string in SQL

Question

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.

Answer 1

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.