Regex in LIKE Clause that accepts only Alphanumeric and dashes

user_6856285 0
Viewed 659

Below is my SQL function script that will help identify the alphanumeric value and dashes (-):

CREATE   FUNCTION [dbo].[FN_VALIDATE_ALPHANUMERIC_AND_DASHES](@TX_INPUT VARCHAR(1000))RETURNS BIT AS
BEGIN     
    DECLARE @bitInputVal AS BIT = 0
    DECLARE @InputText VARCHAR(1000)

    SET @InputText = LTRIM(RTRIM(ISNULL(@TX_INPUT,'')))
  
    IF @InputText <> ''
    BEGIN
        SET @bitInputVal = CASE
                                WHEN @InputText LIKE '%[A-Za-z0-9-]%' THEN 1
                                ELSE 0
                            END
    END
  RETURN @bitInputVal
END

I have problem which I try this query: SELECT dbo.FN_VALIDATE_CLAIMANT_REF_NO('AbcdefgH-1234*') it gives me a result of 1 though the character * is not included in the regex and should return 0 instead.

What I want to achieve is to explicitly verify if the string consist of alphanumeric (alphabets and numbers) and dashes only.

Please note that there are no limitation in length of the characters, only check if the string consist of alphanumeric and dashes.

sql-like tsql sql-server sql
1 Answers

You are testing for "whether any alphabet, number or dash is present in the string"

You should instead test whether any character other than alphabet, number or dash is present.

SET @bitInputVal = CASE WHEN @InputText LIKE '%[^A-Za-z0-9-]%' THEN 0 ELSE 1 END
user_12522280
Related