22,302
社区成员




CREATE FUNCTION dbo.f_test
(
@s VARCHAR(8000) ,
@col VARCHAR(200)
)
RETURNS INT
AS
BEGIN
DECLARE @re INT=0
DECLARE @i INT = 1
WHILE LEN(@s) > 0
BEGIN
IF @col LIKE '%'+LEFT(@s, 1 )+'%'
BEGIN
SET @re = 1;
BREAK;
END
ELSE
BEGIN
SET @s = STUFF(@s, 1,1, '')
SET @i = @i +1
END
END
RETURN @re
END
GO
IF OBJECT_ID('Table_3') IS NOT NULL
DROP TABLE Table_3
GO
CREATE TABLE [dbo].[Table_3](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Range] [nvarchar](20) NULL,
[Name] [nvarchar](20) NULL
) ON [PRIMARY]
GO
SET NOCOUNT ON;
INSERT INTO [dbo].[Table_3]([Range],[Name])VALUES('12345','AAA')
INSERT INTO [dbo].[Table_3]([Range],[Name])VALUES('23','BBB')
INSERT INTO [dbo].[Table_3]([Range],[Name])VALUES('34','CCC')
INSERT INTO [dbo].[Table_3]([Range],[Name])VALUES('13','DDD')
INSERT INTO [dbo].[Table_3]([Range],[Name])VALUES('23','EEE')
INSERT INTO [dbo].[Table_3]([Range],[Name])VALUES('25','FFF')
------- 以上为测试表及测试数据 ----------
SELECT * FROM dbo.Table_3 AS t
WHERE dbo.f_test('14',Range)=1
--输入参数变量
DECLARE @s NVARCHAR(500)
SET @s='14'
--查询
;WITH cte AS (
SELECT SUBSTRING(@s,sv.number,1) AS singleChar
FROM MASTER.dbo.spt_values AS sv
WHERE sv.type='P' AND sv.number>0 AND sv.number<=LEN(@s)
)
SELECT * FROM dbo.Table_3 AS a
WHERE EXISTS(
SELECT 1 FROM cte AS b WHERE a.Range LIKE '%'+b.singleChar+'%'
)
/*
ID Range Name
----------- -------------------- --------------------
1 12345 AAA
3 34 CCC
4 13 DDD
*/
[/quote]
刚刚测试了,结果正确,是我需要的。
版主真都是大神级别啊!USE tempdb
GO
IF OBJECT_ID('Table_3') IS NOT NULL
DROP TABLE Table_3
GO
CREATE TABLE [dbo].[Table_3](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Range] [nvarchar](20) NULL,
[Name] [nvarchar](20) NULL
) ON [PRIMARY]
GO
SET NOCOUNT ON;
INSERT INTO [dbo].[Table_3]([Range],[Name])VALUES('12345','AAA')
INSERT INTO [dbo].[Table_3]([Range],[Name])VALUES('23','BBB')
INSERT INTO [dbo].[Table_3]([Range],[Name])VALUES('34','CCC')
INSERT INTO [dbo].[Table_3]([Range],[Name])VALUES('13','DDD')
INSERT INTO [dbo].[Table_3]([Range],[Name])VALUES('23','EEE')
INSERT INTO [dbo].[Table_3]([Range],[Name])VALUES('25','FFF')
------- 以上为测试表及测试数据 ----------
SELECT * FROM dbo.Table_3 AS t
WHERE [Range] LIKE '%1%' OR [Range] LIKE '%4%'
/*
ID Range Name
----------- -------------------- --------------------
1 12345 AAA
3 34 CCC
4 13 DDD
*/
--输入参数变量
DECLARE @s NVARCHAR(500)
SET @s='14'
--查询
;WITH cte AS (
SELECT SUBSTRING(@s,sv.number,1) AS singleChar
FROM MASTER.dbo.spt_values AS sv
WHERE sv.type='P' AND sv.number>0 AND sv.number<=LEN(@s)
)
SELECT * FROM dbo.Table_3 AS a
WHERE EXISTS(
SELECT 1 FROM cte AS b WHERE a.Range LIKE '%'+b.singleChar+'%'
)
/*
ID Range Name
----------- -------------------- --------------------
1 12345 AAA
3 34 CCC
4 13 DDD
*/