CODEWITHSUNDEEP
sqlsql-serverregext-sql
Alvin Quezon
Alvin Quezon

Reputation: 1201

Regex number and dashes

I would love to have a regex on my LIKE clause with the criteria given:

Example:

Currently have this function:

CREATE FUNCTION [dbo].[FN_VALIDATE_ID](@TX_INPUT VARCHAR(50))RETURNS BIT AS
BEGIN     
    DECLARE @bitInputVal AS BIT = 1
    DECLARE @InputText VARCHAR(50)

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

Upvotes: 0

Views: 581

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520928

You don't even need a UDF for this, as SQL Server's enhanced LIKE operator can handle this requirement:

SELECT *
FROM yourTable
WHERE col LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]';
--                  \          8 digits              /  -  \   4 digits  /

Upvotes: 3

Related Questions