试一下 DECLARE @Nr INT = 6 ;WITH CTE (NAME,Range ) AS (SELECT 'A',CONVERT(VARCHAR(50),'BBxx1-5,AA44xx8-10') UNION ALL SELECT 'B','CCxx6-7' UNION ALL SELECT 'C','DD11-20' ),CTE2 AS (SELECT NAME ,CONVERT(VARCHAR(100),LEFT(a.Range,CHARINDEX(',',a.Range+ ',')-1)) AS SubRange ,STUFF(a.Range ,1,CHARINDEX(',',a.Range+ ','),'') AS Range FROM CTE a UNION ALL SELECT NAME ,CONVERT(VARCHAR(100),LEFT(a.Range,CHARINDEX(',',a.Range+ ',')-1)) AS SubRange ,STUFF(a.Range ,1,CHARINDEX(',',a.Range+ ','),'') AS Range FROM CTE2 a WHERE a.Range > '' ),CTE3 AS( SELECT a.NAME,REPLACE(RIGHT(a.SubRange,PATINDEX('%[^0-9-]%',REVERSE(a.SubRange))-1)+'-0-0','-','.') AS Range FROM CTE2 a ),CTE4 AS ( SELECT a.NAME,CONVERT(INT,PARSENAME(a.Range,4)) AS MinNr,CONVERT(INT,PARSENAME(a.Range,3)) AS MaxNr FROM CTE3 a ) SELECT * FROM CTE4 a WHERE @Nr BETWEEN a.MinNr AND a.MaxNr
use Tempdb go --> -->
if not object_id(N'Tempdb..#test') is null drop table #test Go Create table #test([Name] nvarchar(21),[Range] nvarchar(100)) Insert #test select N'A',N'BBxx1-5,AA44xx8-10' union all select N'B',N'CCxx6-7' union all select N'C',N'DD11-20' GO DECLARE @RangeValues INT=6 ;WITH T1 AS ( Select [Name],[Range]=STUFF([Range],1,CHARINDEX(',',[Range]+','),''),CAST(LEFT([Range],CHARINDEX(',',[Range]+',')-1) AS VARCHAR(100)) AS [Range1] from #test UNION ALL SELECT [Name],[Range]=STUFF([Range],1,CHARINDEX(',',[Range]+','),''),CAST(LEFT([Range],CHARINDEX(',',[Range]+',')-1) AS VARCHAR(100)) AS [Range1] FROM T1 WHERE [Range]>'' ),T2 AS (SELECT [Name], RStart=CAST(LEFT([Range],CHARINDEX('-',[Range])-1) AS INT), REnd=CAST(SUBSTRING([Range],CHARINDEX('-',[Range])+1,LEN([Range])) AS INT) FROM (SELECT [Name],[Range]=RIGHT([Range1],PATINDEX('%[^-0-9]%',REVERSE([Range1]))-1) FROM T1) AS a ) SELECT Name FROM T2 WHERE @RangeValues BETWEEN T2.RStart AND T2.REnd
create table tab (Name varchar(2),range varchar(50)) insert into tab select 'A','BBxx1-5,AA44xx8-10' insert into tab select 'B','CCxx6-7' insert into tab select 'C','DD11-20'declare @a int set @a=6select name,range,range1=(substring([range],(LEN(range)- patindex('%[a-z]%',REVERSE(range))+2),CHARINDEX('-',range,0)-LEN(range)+patindex('%[a-z]%',REVERSE(range))-2)) ,range2=(SUBSTRING(range,CHARINDEX('-',range)+1,LEN(range)-CHARINDEX('-',range))) into #t from (select a.name,SUBSTRING(range,number,CHARINDEX(',',range+',',number)-1) as range from tab a,master.dbo.spt_values b where b.[type]='P' and CHARINDEX(',',','+range,number )=number )Xselect * from #t where @a between range1 and range2 drop table #t drop table tab
DECLARE @Nr INT = 6
;WITH CTE (NAME,Range ) AS
(SELECT 'A',CONVERT(VARCHAR(50),'BBxx1-5,AA44xx8-10')
UNION ALL
SELECT 'B','CCxx6-7'
UNION ALL
SELECT 'C','DD11-20'
),CTE2 AS
(SELECT NAME
,CONVERT(VARCHAR(100),LEFT(a.Range,CHARINDEX(',',a.Range+ ',')-1)) AS SubRange
,STUFF(a.Range ,1,CHARINDEX(',',a.Range+ ','),'') AS Range
FROM CTE a
UNION ALL
SELECT NAME
,CONVERT(VARCHAR(100),LEFT(a.Range,CHARINDEX(',',a.Range+ ',')-1)) AS SubRange
,STUFF(a.Range ,1,CHARINDEX(',',a.Range+ ','),'') AS Range
FROM CTE2 a
WHERE a.Range > ''
),CTE3 AS(
SELECT a.NAME,REPLACE(RIGHT(a.SubRange,PATINDEX('%[^0-9-]%',REVERSE(a.SubRange))-1)+'-0-0','-','.') AS Range
FROM CTE2 a
),CTE4 AS
(
SELECT a.NAME,CONVERT(INT,PARSENAME(a.Range,4)) AS MinNr,CONVERT(INT,PARSENAME(a.Range,3)) AS MaxNr
FROM CTE3 a
)
SELECT * FROM CTE4 a
WHERE @Nr BETWEEN a.MinNr AND a.MaxNr
go
--> -->
if not object_id(N'Tempdb..#test') is null
drop table #test
Go
Create table #test([Name] nvarchar(21),[Range] nvarchar(100))
Insert #test
select N'A',N'BBxx1-5,AA44xx8-10' union all
select N'B',N'CCxx6-7' union all
select N'C',N'DD11-20'
GO
DECLARE @RangeValues INT=6
;WITH T1
AS
(
Select [Name],[Range]=STUFF([Range],1,CHARINDEX(',',[Range]+','),''),CAST(LEFT([Range],CHARINDEX(',',[Range]+',')-1) AS VARCHAR(100)) AS [Range1] from #test
UNION ALL
SELECT [Name],[Range]=STUFF([Range],1,CHARINDEX(',',[Range]+','),''),CAST(LEFT([Range],CHARINDEX(',',[Range]+',')-1) AS VARCHAR(100)) AS [Range1] FROM T1 WHERE [Range]>''
),T2
AS
(SELECT
[Name],
RStart=CAST(LEFT([Range],CHARINDEX('-',[Range])-1) AS INT),
REnd=CAST(SUBSTRING([Range],CHARINDEX('-',[Range])+1,LEN([Range])) AS INT)
FROM (SELECT [Name],[Range]=RIGHT([Range1],PATINDEX('%[^-0-9]%',REVERSE([Range1]))-1) FROM T1) AS a
)
SELECT Name FROM T2 WHERE @RangeValues BETWEEN T2.RStart AND T2.REnd
insert into tab select 'A','BBxx1-5,AA44xx8-10'
insert into tab select 'B','CCxx6-7'
insert into tab select 'C','DD11-20'declare @a int
set @a=6select name,range,range1=(substring([range],(LEN(range)- patindex('%[a-z]%',REVERSE(range))+2),CHARINDEX('-',range,0)-LEN(range)+patindex('%[a-z]%',REVERSE(range))-2))
,range2=(SUBSTRING(range,CHARINDEX('-',range)+1,LEN(range)-CHARINDEX('-',range))) into #t from
(select a.name,SUBSTRING(range,number,CHARINDEX(',',range+',',number)-1) as range
from tab a,master.dbo.spt_values b
where b.[type]='P' and CHARINDEX(',',','+range,number )=number
)Xselect * from #t
where @a between range1 and range2
drop table #t
drop table tab