SQL Server 是否有方法限制插入到字符串中的数字数量(varchar)

wrrgggsh  于 2022-11-21  发布在  其他
关注(0)|答案(1)|浏览(113)

我最近刚开始使用SQL,任务是创建一个标量函数。我创建了一个字符串(varchar),它最多允许13个字符(数字和-),如果可能的话,我希望限制这个字符串。现在我可以插入13个数字,但我只希望它允许12个数字和一个额外的符号,在本例中是'-'。
如果我在字符串中包含13个数字(而不是符号),有没有办法限制打印字符串?

declare @personNr varchar(13)  
declare @atpos int              
declare @atpos2 int             
declare @cnt int                

set @personNr = '990511-8346'

    set @atpos = CHARINDEX('-', reverse(@personNr))
    set @atpos2 = CHARINDEX('+', reverse(@personNr))

    if @atpos = 5 or @atpos2 = 5 or @personNr NOT LIKE '%[^0-9]%' 
        print '1'
    else
        print '0'

现在它允许打印,即使我用另一个数字交换'-'符号,我不想这样。有什么办法限制这一点吗?谢谢!

p1tboqfb

p1tboqfb1#

一种方法是REPLACE连字符(-),检查字符串是否为12个字符长且不包含其他字符,如果不是REPLACE d,则检查字符串是否为13个字符:

DECLARE @personNr varchar(13) = '990511-8346';

IF LEN(@personNr) = 13 AND LEN(REPLACE(@personNr,'-','')) = 12 AND LEN(REPLACE(@personNr,'-','')) NOT LIKE '%[^0-9]%'
    PRINT N'Value is ok.';
ELSE
    PRINT N'Value is not ok.';

--Value is not ok, as it is only 10 digits in length
GO
DECLARE @personNr varchar(13) = '99051100-8346';

IF LEN(@personNr) = 13 AND LEN(REPLACE(@personNr,'-','')) = 12 AND LEN(REPLACE(@personNr,'-','')) NOT LIKE '%[^0-9]%'
    PRINT N'Value is ok.';
ELSE
    PRINT N'Value is not ok.';

--Value is ok, as it is 12 digits in length, and contains 1 hyphen
GO
DECLARE @personNr varchar(13) = '990511-0-8346';

IF LEN(@personNr) = 13 AND LEN(REPLACE(@personNr,'-','')) = 12 AND LEN(REPLACE(@personNr,'-','')) NOT LIKE '%[^0-9]%'
    PRINT N'Value is ok.';
ELSE
    PRINT N'Value is not ok.';

--Value is not OK, as it has 2 hyphens

相关问题