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.

• Tony Hinkle: When you say "like," it is unclear exactly what is needed. Will it always be four numbers followed by a dot and two uppercase letters? If not, all possible permutations are needed.
• rg1105: always 4 numbers followed by a dot and then two uppercase letters
• rg1105: sorry, only sql server
• Hannah Vernon: It's unclear what you're asking...What's not working? are you trying to use SSMS to replace values in a query? Or Is 3708.DK a value stored in a table that you want to change? Be aware, T-SQL doesn't natively support any kind of "regex", like C# or Python.

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;