Is there a way to limit the amount of numbers inserted into a string (varchar)-CodePudding

I recently started with SQL and have been tasked with creating a scalar function. I've made a string (varchar) which allows a maximum of 13 characters (numbers and -), and I want to limit this if possible. Right now I can insert 13 numbers without any issue, however I only want it to allow 12 numbers and one extra symbol, in this case '-'.

Is there a way to limit my string from printing if I include 13 numbers (and not the symbol) in it?

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'

Right now it allows printing even if i interchange the '-' symbol with another number, which I don't want. Is there a way to limit this? Thank you!

CodePudding user response：

One method would be the REPLACE the hyphen (-) and check the string is 12 characters long and contains no other characters, as well as check it's 13 characters when it's not REPLACEd:

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