我试图在sql Server查询中重写多个字符,并希望通过#temp表而不是嵌套的REPLACE来实现.我下面有sql代码,希望实现结果
ABC
DEF
GHI
IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp
IF OBJECT_ID('tempdb..#temp2') IS NOT NULL DROP TABLE #temp2
CREATE TABLE #temp
(
STRING_TO_REPLACE NVARCHAR(5)
)
INSERT INTO #temp (STRING_TO_REPLACE)
VALUES (' '),('/'),('_')
CREATE TABLE #temp2
(
STRING_NAME NVARCHAR(5)
)
INSERT INTO #temp2 (STRING_NAME)
VALUES ('A BC'),('D/EF'),('G_HI')
SELECT REPLACE(t2.STRING_NAME,(SELECT t1.STRING_TO_REPLACE
FROM #temp t1),'')
FROM #temp2 t2
IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp
IF OBJECT_ID('tempdb..#temp2') IS NOT NULL DROP TABLE #temp2
我可以通过嵌套替换来实现结果
SELECT REPLACE(REPLACE(REPLACE(t2.STRING_NAME,'_',''),'/',' ','') FROM #temp2 t2
但真的很想通过#temp表来做到这一点.请有人帮助我这个.
当我尝试运行我的第一个代码,我得到以下错误
Msg 512,Level 16,State 1,Line 23 Subquery returned more than 1
value. This is not permitted when the subquery follows =,!=,<,<=,,>= or when the subquery is used as an expression.
解决方法
这是使用CROSS APPLY的一种方法
SELECT result
FROM #temp2 t2
CROSS apply (SELECT Replace(string_name,t1.string_to_replace,'') AS
result
FROM #temp t1) cs
WHERE result <> string_name
结果：
result ----- ABC DEF GHI
注意：只有每个string_name只有一个string_to_replace,这将是有效的
更新：在单个string_name中处理多个string_to_replace,这里使用Dynamic sql的一种方法
我通过向循环添加一个identity属性对#temp表进行了一个小的更改
IF Object_id('tempdb..#temp') IS NOT NULL
DROP TABLE #temp
IF Object_id('tempdb..#temp2') IS NOT NULL
DROP TABLE #temp2
CREATE TABLE #temp
(
id INT IDENTITY(1,1),string_to_replace NVARCHAR(5)
)
INSERT INTO #temp
(string_to_replace)
VALUES (' '),('_')
CREATE TABLE #temp2
(
string_name NVARCHAR(5)
)
INSERT INTO #temp2
(string_name)
VALUES ('A BC'),('G_HI'),('A BD_')
DECLARE @col_list VARCHAR(8000)= '',@sql VARCHAR(max),@cntr INT,@inr INT =1,@STRING_TO_REPLACE NVARCHAR(5)
SELECT @cntr = Max(id)
FROM #temp
SET @sql = 'select '
WHILE @inr < = @cntr
BEGIN
SELECT @STRING_TO_REPLACE = string_to_replace
FROM #temp
WHERE id = @inr
IF @inr = 1
SET @col_list = 'replace (STRING_NAME,'''
+ @STRING_TO_REPLACE + ''','''')'
ELSE
SET @col_list = 'replace (' + @col_list + ','''')'
SET @inr+=1
END
SET @sql += ' from #temp2'
--print @col_list
SET @sql = 'select ' + @col_list + ' as Result from #temp2'
--print @sql
EXEC (@sql)
结果：
Result ------ ABC DEF GHI ABD
