27,579
社区成员
发帖
与我相关
我的任务
分享
USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(
id VARCHAR(50) PRIMARY KEY,
uname NVARCHAR(20)
)
GO
INSERT INTO t VALUES ('2018041A1243HS001','a')
INSERT INTO t VALUES ('2018041A1243HS002','b')
INSERT INTO t VALUES ('2018041A1243HS003','c')
INSERT INTO t VALUES ('2018041A1243HS004','d')
GO
------ 以上为测试表 ------
--增加专门的过滤数字的函数
IF OBJECT_ID('dbo.Fun_GetCharList ') IS NOT NULL
DROP FUNCTION dbo.Fun_GetCharList
GO
-- =============================================
-- Author: yenange
-- Create date: 2018-11-13
-- Description:
-- =============================================
CREATE FUNCTION dbo.Fun_GetCharList
(
@str NVARCHAR(MAX)
)
RETURNS
@table TABLE
(
rowNum INT IDENTITY(1,1),
item NVARCHAR(MAX)
)
AS
BEGIN
DECLARE @tmp NVARCHAR(MAX),@single NVARCHAR(MAX),@idx INT,@flag BIT
SET @idx=1;
SET @flag=0;
WHILE @idx<=LEN(@str)+1
BEGIN
SET @tmp=SUBSTRING(@str,@idx,1)
--如果为数字,则累加
IF (@tmp>='0' AND @tmp<='9')
OR
(@tmp>='a' AND @tmp<='z')
OR
(@tmp>='A' AND @tmp<='Z')
BEGIN
SET @single=ISNULL(@single,'')+@tmp;
SET @flag=1
END
--否则
ELSE IF @flag=1
BEGIN
INSERT INTO @table(item)
SELECT @single
SET @single=''
SET @flag=0
END
SET @idx=@idx+1
END
RETURN
END
GO
--查询
DECLARE @s NVARCHAR(MAX)=N'2018041A1243HS001
2
3'
--
SELECT * FROM t WHERE id IN (
SELECT item FROM dbo.Fun_GetCharList(@s)
)
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([id] int,[text] nvarchar(21),[name] nvarchar(21))
Insert #T
select 1,N'a',N'张' union all
select 2,N'b',N'王' union all
select 3,N'c',N'刘'
Go
--测试数据结束
SELECT
*
FROM
#T
WHERE
id IN (
SELECT
value
FROM
dbo.F_Split( REPLACE(REPLACE('1 2 ',CHAR(13), ''),CHAR(10), ''),' ') --这里的1 2 是复制出来的值 后边的空格是分隔用的
);
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([id] int,[text] nvarchar(21),[name] nvarchar(21))
Insert #T
select 1,N'a',N'张' union all
select 2,N'b',N'王' union all
select 3,N'c',N'刘'
Go
--测试数据结束
SELECT
*
FROM
#T
WHERE
id IN (
SELECT
value
FROM
dbo.F_Split( REPLACE(REPLACE('1 2 ',CHAR(13), ''),CHAR(10), ''),' ') --这里的1 2 是复制出来的值 后边的空格是分隔用的
);
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([id] int,[text] nvarchar(21),[name] nvarchar(21))
Insert #T
select 1,N'a',N'张' union all
select 2,N'b',N'王' union all
select 3,N'c',N'刘'
Go
--测试数据结束
SELECT
*
FROM
#T
WHERE
id IN (
SELECT
value
FROM
dbo.F_Split( REPLACE(REPLACE('1 2 ',CHAR(13), ''),CHAR(10), ''),' ') --这里的1 2 是复制出来的值 后边的空格是分隔用的
);
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([id] int,[text] nvarchar(21),[name] nvarchar(21))
Insert #T
select 1,N'a',N'张' union all
select 2,N'b',N'王' union all
select 3,N'c',N'刘'
Go
--测试数据结束
SELECT
*
FROM
#T
WHERE
id IN (
SELECT
value
FROM
dbo.F_Split( REPLACE(REPLACE('1 2 ',CHAR(13), ''),CHAR(10), ''),' ') --这里的1 2 是复制出来的值 后边的空格是分隔用的
);
USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(
id INT PRIMARY KEY,
uname NVARCHAR(20)
)
GO
INSERT INTO t VALUES (1,'a')
INSERT INTO t VALUES (2,'b')
INSERT INTO t VALUES (3,'c')
INSERT INTO t VALUES (4,'d')
GO
------ 以上为测试表 ------
--增加专门的过滤数字的函数
IF OBJECT_ID('dbo.Fun_GetNumList ') IS NOT NULL
DROP FUNCTION dbo.Fun_GetNumList
GO
-- =============================================
-- Author: yenange
-- Create date: 2018-11-13
-- Description:
-- =============================================
CREATE FUNCTION dbo.Fun_GetNumList
(
@str NVARCHAR(MAX)
)
RETURNS
@table TABLE
(
rowNum INT IDENTITY(1,1),
item NVARCHAR(20)
)
AS
BEGIN
DECLARE @tmp NVARCHAR(MAX),@single NVARCHAR(MAX),@idx INT,@flag BIT
SET @idx=1;
WHILE @idx<=LEN(@str)
BEGIN
SET @tmp=SUBSTRING(@str,@idx,1)
--如果为数字,则累加
IF @tmp>='0' AND @tmp<='9'
BEGIN
SET @single=ISNULL(@single,'')+@tmp;
SET @flag=1
END
--否则
ELSE IF ISNUMERIC(@single)=1
BEGIN
INSERT INTO @table(item)
SELECT @single
SET @single=''
SET @flag=0
END
SET @idx=@idx+1
END
IF ISNUMERIC(@single)=1
BEGIN
INSERT INTO @table(item)
SELECT @single
END
RETURN
END
GO
--查询
DECLARE @s NVARCHAR(MAX)=N'1
2
3
'
--
SELECT * FROM t WHERE id IN (
SELECT item FROM dbo.Fun_GetNumList(@s)
)
SELECT
*
FROM
表
WHERE
id IN (
SELECT
value
FROM
dbo.F_Split( REPLACE(REPLACE('1 2 ',CHAR(13), ''),CHAR(10), ''),' ') --这里的1 2 是复制出来的值 后边的空格是分隔用的
);
CREATE FUNCTION dbo.F_Split
(
@SplitString nvarchar(max), --源字符串
@Separator nvarchar(10)=' ' --分隔符号,默认为空格
)
RETURNS @SplitStringsTable TABLE --输出的数据表
(
[id] int identity(1,1),
[value] nvarchar(max)
)
AS
BEGIN
DECLARE @CurrentIndex int;
DECLARE @NextIndex int;
DECLARE @ReturnText nvarchar(max);
SELECT @CurrentIndex=1;
WHILE(@CurrentIndex<=len(@SplitString))
BEGIN
SELECT @NextIndex=charindex(@Separator,@SplitString,@CurrentIndex);
IF(@NextIndex=0 OR @NextIndex IS NULL)
SELECT @NextIndex=len(@SplitString)+1;
SELECT @ReturnText=substring(@SplitString,@CurrentIndex,@NextIndex-@CurrentIndex);
INSERT INTO @SplitStringsTable([value]) VALUES(@ReturnText);
SELECT @CurrentIndex=@NextIndex+1;
END
RETURN;
END
GO
SELECT
*
FROM
表
WHERE
id IN (
SELECT
value
FROM
dbo.F_Split('1 2', ' ') --这里的1 2 是复制出来的值 后边的空格是分隔用的
);
--增加一个表值分割函数,这个很常用,一劳永逸
IF OBJECT_ID('[dbo].[Fun_Split]') IS NOT NULL
DROP FUNCTION [dbo].[Fun_Split]
GO
CREATE FUNCTION [dbo].[Fun_Split](@str NTEXT, @split NVARCHAR(10))
RETURNS @table TABLE (rid INT IDENTITY(1,1) PRIMARY KEY,[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 @s NVARCHAR(MAX)
SET @s='1,2'
SELECT * FROM tableName WHERE id IN (
SELECT item FROM dbo.Fun_Split(@s,',')
)