试图在我的数据库中替换一些字符串,我有两个表。table_2上的替换使用第一次替换的结果作为输入:
当前状态(字符串只是一个替代,它可以是任何东西,重要的部分是破折号):
|table_1 - col1| |table_2 - col1 |
---------------- ------------------------------
|string-1 | |text string-1 text string-3 |
|string-2 | |text string-3 string-2 t-ext|
|string-3 | |string-2 text string-3 te-xt|
预期的结果:
|table_1 - col1 | |table_2 - col1 |
----------------- ------------------------------
|string_1 | |text string_1 text string_3 |
|string_2 | |text string_3 string_2 t-ext|
|string_3 | |string_2 text string_3 te-xt|
简单地说,我想用table_1中的_
替换-
,并在table_2上执行相应的替换。
想到了第一部分,但我想不出表2上的替代部分:
SELECT col1, REPLACE(col1, '-', '_') as Replacement
FROM table_1
where col1 like '%-%'
我需要做这样的事情(这段代码是错误的):
SELECT REPLACE(col1,
SELECT [col1] FROM [table_1] where col1 like '%-%',
SELECT REPLACE([col1], '-', '_') FROM [table_1] where col1 like '%-%')
from table_2
替换次数不超过2次
SELECT t2.col2, REPLACE(REPLACE(t2.col2,t1.col1,REPLACE(t1.col1, '-', '_')),t3.col1,REPLACE(t3.col1, '-', '_'))
FROM table_2 t2
JOIN table_1 t1 ON t2.col2 like '%' +t1.col1+'%' AND t1.col1 LIKE '%-%'
LEFT JOIN table_1 t3 ON t3.col1 <> t1.col1 AND t2.col2 LIKE '%'+t3.col1+'%' AND t3.col1 LIKE '%-%'
WHERE t2.col2 LIKE '%-%'
这完全是临时的,不需要递归:
DECLARE @table_1 TABLE(ID INT IDENTITY,col1 VARCHAR(100));
INSERT INTO @table_1 VALUES
('string-1'),('string-2'),('string-3');
DECLARE @table_2 TABLE(ID INT IDENTITY,col1 VARCHAR(100));
INSERT INTO @table_2 VALUES
('text string-1 text string-3'),('text string-3 string-2 t-ext'),('string-2 text string-3 te-xt');
——第一个CTE替换t1
中的值WITH t1New AS
(
SELECT ID AS t1_ID
,t1.col1 AS t1c1
,REPLACE(t1.col1,'-','_') AS new_t1c1
FROM @table_1 AS t1
)
——第二个CTE在空格
上分割t2的字符串,t2Splitted AS
(
SELECT ID
,col1 AS t2c1
,CAST('<x>' + REPLACE(t2.col1,' ','</x><x>') + '</x>' AS XML) AS Casted
FROM @table_2 AS t2
)
——此CTE将值映射到拆分的部分
,Mapped AS
(
SELECT t1New.*
,t2Splitted.ID AS t2_ID
,ROW_NUMBER() OVER(PARTITION BY t2Splitted.ID ORDER BY (SELECT NULL)) AS PartIndex
,part.value('.','nvarchar(max)') AS Part
FROM t2Splitted
CROSS APPLY t2Splitted.Casted.nodes('/x') AS A(part)
LEFT JOIN t1New ON t1New.t1c1=part.value('.','nvarchar(max)')
)
——如果存在映射,则取新值,否则取旧值
,NewValues AS
(
SELECT *
,CASE WHEN t1c1 IS NOT NULL THEN new_t1c1 ELSE Part END AS newValue
FROM Mapped
)
——最后的CTE按照原顺序用空格重新连接字符串
,Final AS
(
SELECT nv1.t2_ID
,(SELECT ' ' + nv2.newValue
FROM NewValues AS nv2
WHERE nv2.t2_ID=nv1.t2_ID
ORDER BY PartIndex
FOR XML PATH('')) AS FinalValue
FROM NewValues AS nv1
GROUP BY nv1.t2_ID
)
——最后一个值用于更新原始表
UPDATE t2 SET t2.col1=Final.FinalValue
FROM @table_2 AS t2
INNER JOIN Final ON Final.t2_ID=t2.ID
由你决定:UPDATE t1,这是一行代码,并去掉FinalValue:-)
中的尾随空格SELECT * FROM @table_2
基于变量的替换可以通过替换表来完成。
DECLARE @Raw NVARCHAR(MAX) = '...';
SELECT @Raw= REPLACE(@Raw, P, R)
FROM (VALUES ('string-1', 'string_1'),
('string-2','string_2'),
('string-3','string_3'),
('string-4','string_4'),
) AS T(P, R);
要对表执行相同的逻辑,请考虑以下语句:
SELECT col1, MultipleReplace(col1, replacement_table(P, R))
FROM some_table
创建一个接受字符串输入和替换表的函数。为了将表传递给函数,我们必须创建一个表类型。
CREATE TYPE dbo.MulReplacements AS TABLE
(
Pattern NVARCHAR(MAX) NOT NULL,
Replacement NVARCHAR(MAX) NOT NULL
)
则函数为
CREATE FUNCTION dbo.MulReplace(
@string AS NVARCHAR(MAX),
@replacements AS dbo.MulReplacements READONLY
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @result AS NVARCHAR(MAX) = @string;
SELECT @result = REPLACE(@result, R.Pattern, R.Replacement)
FROM @replacements AS R;
RETURN @result;
END
all together
DECLARE @replacement AS dbo.MulReplacements;
INSERT INTO @replacement
SELECT col1, REPLACE(col1, '-', '_')
FROM (VALUES ('string-1'), ('string-2'), ('string-3')) AS table_1(col1)
SELECT col1, dbo.Mulreplace(col1, @replacement)
FROM (VALUES ('text string-1 text string-3'), ('text string-3 string-2 t-ext'), ('string-2 text string-3 te-xt')) AS table_2(col1)
动态查询的一种方法。替换实际的表名和列名(注释要更改的地方)。
DECLARE @colNames VARCHAR(MAX) = ''
SELECT @colNames = @colNames + ', [' + table1_Col1 + ']' FROM tableName1 -- Table1 Column and Table1 Name
DECLARE @ReqColNames VARCHAR(MAX) = STUFF(@colNames, 1, 1, '')
DECLARE @int int
SELECT @int = count(*) FROM tableName1 -- Table1 Name
DECLARE @replace varchar(max) = replicate('REPLACE(', @int) + 't2.table2_Col2' -- Table2 Column
DECLARE @replaceCols varchar(max) = ''
SELECT @replaceCols = @replaceCols + ', r.[' + table1_Col1 + '], replace(r.[' + table1_Col1 + '], ''-'', ''_''))' FROM tableName1 -- Table1 Column and Table1 Name
DECLARE @ReplaceString varchar(max) = @replace + @replaceCols
DECLARE @cmd varchar(max) = 'SELECT ' + @ReplaceString + ' FROM
(
SELECT * FROM tableName1
PIVOT
(MAX (table1_Col1) FOR table1_Col1 IN (' + @ReqColNames + ')) x
) r
CROSS JOIN tableName2 t2'
EXEC(@cmd)
静态查询:对于上面的代码(显示上面的动态代码正在生成什么):
Select replace(replace(replace(t2.table2_Col2
, r.[string-1], replace(r.[string-1], '-', '_'))
, r.[string-2], replace(r.[string-2], '-', '_'))
, r.[string-3], replace(r.[string-3], '-', '_'))
from
(
Select * from tableName1
PIVOT
(MAX (table1_Col1) FOR table1_Col1 IN ([string-1], [string-2], [string-3])) x
) r
CROSS JOIN tableName2 t2
输出:text string_1 text string_3
text string_3 string_2 t-ext
string_2 text string_3 te-xt