我需要“验证”一个数量。给定任何数字x,如果数字包含在以逗号分隔的范围和数字集合中,则返回true或false。例如:有效数字"1,5-10,25-50,100,500",一些有效数字是1,5,6,7,8,9,10,但不是11,12,51等等。
发布于 2015-08-20 13:36:40
如果将字符串转换为min和max值的临时表,则可以轻松选择有效值。
T (MS SQL Server)中的示例:
declare @valid varchar(50) = '1,5-10,25-50,100,500'
declare @i int, @range varchar(10)
declare @t table(min int, max int)
while len(@valid) > 0 begin
set @i = charindex(',', @valid)
if @i = 0 begin
set @range = @valid
set @valid = ''
end else begin
set @range = left(@valid, @i - 1)
set @valid = right(@valid, len(@valid) - @i)
end
set @i = charindex('-', @range)
if @i = 0 begin
insert into @t (min, max) values (cast(@range as int), cast(@range as int))
end else begin
insert into @t (min, max) values (cast(left(@range, @i - 1) as int), cast(right(@range, len(@range) - @i) as int))
end
end
select
n
from
(values(1),(5),(6),(7),(8),(9),(10),(11),(12),(51)) as x (n)
inner join @t on n between min and max
发布于 2015-08-20 13:40:02
检查此选项( Server 2008)
CREATE FUNCTION [dbo].[RunningNumbers](@anzahl INT=1000000, @StartAt INT=0)
RETURNS TABLE
AS
RETURN
WITH E1(N) AS(SELECT 1 FROM(VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))t(N)), --10 ^ 1
E2(N) AS(SELECT 1 FROM E1 a CROSS JOIN E1 b), -- 10 ^ 2 = 100 rows
E4(N) AS(SELECT 1 FROM E2 a CROSS JOIN E2 b), -- 10 ^ 4 = 10,000 rows
E8(N) AS(SELECT 1 FROM E4 a CROSS JOIN E4 b), -- 10 ^ 8 = 10,000,000 rows
CteTally AS
(
SELECT TOP(ISNULL(@anzahl,1000000)) ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) -1 + ISNULL(@StartAt,0) As Nmbr
FROM E8
)
SELECT * FROM CteTally;
GO
CREATE FUNCTION dbo.CheckRange
(
@ValidTarget INT
,@rangeList VARCHAR(MAX)
)
RETURNS BIT
AS
BEGIN
DECLARE @rangeParts XML=CAST('<root><r>'+REPLACE(@rangeList,',','</r><r>') + '</r></root>' AS XML);
DECLARE @Count INT;
SELECT @Count= COUNT(*)
FROM
(
SELECT Valid.X
FROM @rangeParts.nodes('/root/r') AS rp(p)
CROSS APPLY
(
SELECT CASE WHEN CHARINDEX('-',p.value('.','varchar(max)'))>0
THEN CAST('<root><r>'+REPLACE(p.value('.','varchar(max)'),'-','</r><r>') + '</r></root>' AS XML)
ELSE '<root><r>' + p.value('.','varchar(max)') + '</r><r>' + p.value('.','varchar(max)') + '</r></root>' END AS pr
) AS partsResolved
CROSS APPLY
(
SELECT CASE WHEN @ValidTarget BETWEEN partsResolved.pr.value('(/root/r)[1]','int') AND partsResolved.pr.value('(/root/r)[2]','int') THEN 1 ELSE 0 END AS X
) AS Valid
) AS tbl
WHERE tbl.X=1
RETURN @Count;
END
GO
SELECT Nmbr,dbo.CheckRange(Nmbr,'1,5-10,25-50,100,500')
FROM dbo.RunningNumbers(70,-3)
DROP FUNCTION dbo.RunningNumbers;
GO
DROP FUNCTION dbo.CheckRange;
https://stackoverflow.com/questions/32119069
复制相似问题