27,579
社区成员
发帖
与我相关
我的任务
分享
declare @A varchar(100)='生菜,b,c'
Update [ItemInfo] set [ItemName]='结球生菜1' WHERE [ItemName] IN(
select item FROM [dbo].[Fun_String2ToStringArray](@A,',')
)
USE tempdb
GO
IF OBJECT_ID('test') IS NOT NULL DROP TABLE test
GO
CREATE TABLE test(
ItemName NVARCHAR(20)
)
GO
SET NOCOUNT ON
INSERT INTO test VALUES ('a')
INSERT INTO test VALUES ('b')
INSERT INTO test VALUES ('c')
INSERT INTO test VALUES ('d')
GO
--1. 以上为测试表和测试数据
--2. 添加表值函数
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
--3. 查询
--3.1 用表值函数得到结果:
SELECT * FROM test WHERE [ItemName] IN(
select item FROM [dbo].[Fun_String2ToStringArray]('a,b,c',',')
)
--3.2 用 like 得到结果:
SELECT * FROM test WHERE ','+'a,b,c'+',' LIKE '%,'+ItemName+',%'
SELECT * FROM [dbo].[ItemInfo] WHERE [ItemName] IN(select ''''+replace(replace(@A,'"',''),',',''',''')+'''')
[/quote]
用动态语句试试。
declare @A varchar(100)
declare @sql varchar(1000)
set @A='"a,b,c"'
set @sql='SELECT * FROM [dbo].[ItemInfo] WHERE [ItemName] IN ('''+replace(replace(@A,'"',''),',',''',''')+''')'
exec(@sql)
SELECT * FROM [dbo].[ItemInfo] WHERE [ItemName] IN(select ''''+replace(replace(@A,'"',''),',',''',''')+'''')
declare @A varchar(100)
set @A='"a,b,c"'
select ''''+replace(replace(@A,'"',''),',',''',''')+''''
--添加表值函数
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
--查询
SELECT ''''+item+'''' AS r FROM [dbo].[Fun_String2ToStringArray]('a,b,c',',')
/*
r
----
'a'
'b'
'c'
*/