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.
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this communityIf 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;
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.