SQL regex to identify alphanumeric values

Question

I am trying to exclude values like 3708.DK in sql. I'm using pattern like LIKE '%0-9.%' and '%[0-9.A-Z]%' and its not working .

Please suggest.

Answer 1

If you're attempting to exclude rows in a table, then a LIKE comparison would work.

Take this minimally complete verifiable example:

IF OBJECT_ID(N'tempdb..#LikeTest', N'U') IS NOT NULL
DROP TABLE #LikeTest;

CREATE TABLE #LikeTest
(
    d varchar(50) NOT NULL
);

INSERT INTO #LikeTest (d)
VALUES ('this is a 3708.dk test')
    , ('this is another test');

Here's the pertinent bit:

SELECT lt.d
    ,  Match = CASE WHEN lt.d LIKE '%[0-9][0-9][0-9][0-9]\.[a-z][a-z]%' 
                ESCAPE '\' 
            THEN 1 ELSE 0 END
FROM #LikeTest lt;

Results:

╔════════════════════════╦═══════╗
║           d            ║ Match ║
╠════════════════════════╬═══════╣
║ this is a 3708.dk test ║     1 ║
║ this is another test   ║     0 ║
╚════════════════════════╩═══════╝

The LIKE comparison above says "match where the data contains 4 consecutive digits, followed by a period, followed by two alpha characters in the range of [a-z]. Be aware the LIKE statement is collation-sensitive; if you have a server or database collation that is case sensitive, my example LIKE won't match upper case alpha characters. You can force a case-insensitive search by adding a COLLATE clause to the LIKE comparison, like this:

SELECT lt.d
    ,  Match = CASE WHEN lt.d LIKE '%[0-9][0-9][0-9][0-9]\.[a-z][a-z]%' 
                ESCAPE '\' 
                COLLATE SQL_Latin1_General_CP1_CI_AS
            THEN 1 ELSE 0 END
FROM #LikeTest lt;