时间戳

首页 > 解决方案 > How can I replace multiple words of a string in SQL

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

标签： sqlsql-server

解决方案

考虑到 sql server 是您唯一的工具（不是 ac# 或其他应用程序），作为一种工作方法；使用临时表或持久表来存储替换选项。

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

结果是：**一些字符串值**CTN **一些字符串值** POLY。

推荐阅读