27,582
社区成员




1、replace('ab','abc,abd','') = abc,abd
2、replace('ab','ab,abd','') = abd
3、replace('ab','abc,ab','') = abc
USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(
v NVARCHAR(20)
)
GO
INSERT INTO t VALUES ('abc,abd')
INSERT INTO t VALUES ('ab,abd')
INSERT INTO t VALUES ('abc,ab')
------------ 以上为测试表及测试数据
------------ 增加一个表值分割函数
IF OBJECT_ID('[dbo].[Fun_String2ToStringArray]') IS NOT NULL
DROP FUNCTION [dbo].[Fun_String2ToStringArray]
GO
CREATE FUNCTION [dbo].[Fun_String2ToStringArray](@str NVARCHAR(MAX), @split NVARCHAR(10))
RETURNS @table TABLE ([item] NVARCHAR(max))
AS
BEGIN
IF LEN(@split) = 0
BEGIN
SET @split = N','
END
DECLARE @xml XML;
SET @xml = CONVERT(XML, '<x><![CDATA[' + replace(CONVERT(VARCHAR(MAX), @str), @split, ']]></x><x><![CDATA[') + ']]></x>')
INSERT INTO @table
SELECT item
FROM (SELECT c.value('text()[1]', 'nvarchar(4000)') [item]
FROM @xml.nodes('/x') t(c)) t
WHERE item IS NOT NULL
RETURN
END
GO
------------------ 查询
DECLARE @search1 NVARCHAR(20),@search2 NVARCHAR(20)
SET @search1='ab'
SET @search2='abc'
SELECT
v
,ISNULL((SELECT item FROM [dbo].[Fun_String2ToStringArray](t.v,',') WHERE item NOT IN (@search1,@search2) ),'') AS vResult
FROM t
/*
v vResult
-------------------- -------------
abc,abd abd
ab,abd abd
abc,ab
*/
USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(
v NVARCHAR(20)
)
GO
INSERT INTO t VALUES ('abc,abd')
INSERT INTO t VALUES ('ab,abd')
INSERT INTO t VALUES ('abc,ab')
DECLARE @search NVARCHAR(20)
SET @search1='ab'
版主,如果对应查找的变量有2个,应该怎么修改达到结果。
@search1='ab'
@search2='abc'
v vResult
-------------------- -------------
abc,abd abd
ab,abd abd
abc,ab
*/
USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(
v NVARCHAR(20)
)
GO
INSERT INTO t VALUES ('abc,abd')
INSERT INTO t VALUES ('ab,abd')
INSERT INTO t VALUES ('abc,ab')
DECLARE @search NVARCHAR(20)
SET @search='ab'
SELECT
v
,CASE WHEN left(t2.vResult,1)=',' THEN SUBSTRING(t2.vResult,2,LEN(t2.vResult))
WHEN RIGHT(t2.vResult,1)=',' THEN SUBSTRING(t2.vResult,1,LEN(t2.vResult)-1)
ELSE t2.vResult END AS vResult
FROM (
SELECT *
,CASE WHEN ','+v+',' LIKE '%,'+@search+',%' THEN REPLACE(','+v+',',','+@search+',','') ELSE v END AS vResult
FROM t
) AS t2
/*
v vResult
-------------------- -------------
abc,abd abc,abd
ab,abd abd
abc,ab abc
*/
create table test(name varchar(30))
go
insert into test values('abc,abd'),('ab,abd'),('abc,ab')
go
-- 前后的逗号,你自己处理一下。
declare @f varchar(10) = 'ab'
select name, replace(',' + name + ',', ',' + @f + ',', '') new_name from test
go
drop table test
go
(3 行受影响)
name new_name
------------------------------ ----------------------------------------------
abc,abd ,abc,abd,
ab,abd abd,
abc,ab ,abc
(3 行受影响)