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)
)