如何用sql中的列替换字符串中的文本[复制]

This question is an exact duplicate of:

这个问题与以下内容完全相同:

How to replace a string with values from columns in a table in SQL 1 answer

如何使用SQL 1答案中的表中的列替换值

Exp                                 Major       Start
__________________________________________________________
                                   |        |
'My names are W.Major and W.Start' |  Hal   |      Bark
___________________________________|________|_________________
'W.Major is a doctor'              |  Mark  |      Slope
___________________________________|________|_______________

Hi All suppose I have the table above in SQL server management studio and for any text in the Exp column I want to replace W.Major with the value in the Major column and wherever there is a W.Start I want to replace it with the value in the Start column.

大家好。假设我在SQL服务器管理工作室中有上表,对于Exp列中的任何文本,我想用Major列中的值替换W.Major,无论哪里有W.Start我想用它替换它“开始”列中的值。

Do you know what type of SP I have to write to get this accomplished?

你知道我必须写什么类型的SP来实现这个目标吗?

2 个解决方案

#1

Well you can use a Dynamic SQL and UNPIVOT to get table with all unit replacements and then you run a while loop to replace expressions one by one till you get your result.

那么你可以使用动态SQL和UNPIVOT来获取包含所有单位替换的表,然后运行while循环来逐个替换表达式,直到得到结果。

I am sure there can be better techniques but here is my solution. Please mark it as answer for my effort :)

我相信可以有更好的技术,但这是我的解决方案。请将其标记为我努力的答案:)

IF OBJECT_ID ('tempdb.dbo.#temptable') IS NOT NULL DROP TABLE #temptable
CREATE TABLE #tempTable  (  ID INT IDENTITY(1, 1),  [Exp] nvarchar(4000) not NULL,  replacementWord nvarchar(50) not null, Wordvalue nvarchar(50) not null, flag  bit null, ReplacedExp nvarchar(4000) null)
DECLARE @query VARCHAR(4000)
DECLARE @queryRWords VARCHAR(2000)
SELECT @queryRWords =
        STUFF((select DISTINCT '],['+ LTRIM(C.name) from sys.Columns  C INNER JOIN  sys.objects O on O.object_id=C.object_id where  O.name='yourtable' and O.type_desc='USER_TABLE' and C.name not like 'Exp' ORDER BY '],['+LTRIM(C.name) FOR XML PATH('') ),1,2,'') + ']'
SET @query='INSERT INTO #tempTable(Exp, replacementWord, WordValue) select [Exp], [replacementWord],[WordValue] FROM (SELECT [Exp], [major],[start] FROM [yourtable]) p
UNPIVOT([Wordvalue] FOR [replacementWord] IN ('+@queryRWords+'))AS unpvt'
EXECUTE(@query)
UPDATE #tempTable SET ReplacedExp=[Exp]
DECLARE @ExpCount INT
SELECT @ExpCount=  COUNT(*) FROM #tempTable
WHILE @ExpCount >0
BEGIN

    IF((SELECT [Exp] From #tempTable where Id=@ExpCount)<>(SELECT [Exp] from #tempTable where Id=(@ExpCount-1)))
        BEGIN 
            UPDATE #tempTable   SET FLAG=1, ReplacedExp=  REPLACE(ReplacedExp, CAST('W.'+replacementWord AS VARCHAR), WordValue) FROM    #tempTable WHERE Id=@ExpCount
        END
    ELSE
        BEGIN
            UPDATE #tempTable SET ReplacedExp=  REPLACE(ReplacedExp, CAST('W.'+replacementWord AS VARCHAR), WordValue) FROM    #tempTable WHERE Id=@ExpCount
            UPDATE #tempTable SET  ReplacedExp= (SELECT ReplacedExp FROM #temptable where Id=@ExpCount) WHERE Id=(@ExpCount-1)
        END
SET @ExpCount=@ExpCount-1
END
UPDATE #tempTable
SET flag=1 where Id=1

SELECT ReplacedExp FROM #tempTable where flag=1

#2

Here is the sample TSQL where you can have a good start

以下是TSQL示例,您可以从中获得良好的开端

SELECT  Exp, Major, Start
        , Replace(Replace(Exp, 'W.Major', Major), 'W.Start', Start) As Result
FROM    [Your Table Name]

#1