首页
学习
活动
专区
圈层
工具
发布
首页
学习
活动
专区
圈层
工具
MCP广场
社区首页 >问答首页 >SQL:验证给定逗号分隔范围集的数量。

SQL:验证给定逗号分隔范围集的数量。
EN

Stack Overflow用户
提问于 2015-08-20 13:05:42
回答 2查看 289关注 0票数 0

我需要“验证”一个数量。给定任何数字x,如果数字包含在以逗号分隔的范围和数字集合中,则返回true或false。例如:有效数字"1,5-10,25-50,100,500",一些有效数字是1,5,6,7,8,9,10,但不是11,12,51等等。

EN

回答 2

Stack Overflow用户

回答已采纳

发布于 2015-08-20 13:36:40

如果将字符串转换为min和max值的临时表,则可以轻松选择有效值。

T (MS SQL Server)中的示例:

代码语言:javascript
运行
复制
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
票数 2
EN

Stack Overflow用户

发布于 2015-08-20 13:40:02

检查此选项( Server 2008)

代码语言:javascript
运行
复制
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;
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/32119069

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档