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 .
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 community
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;
╔════════════════════════╦═══════╗ ║ d ║ Match ║ ╠════════════════════════╬═══════╣ ║ this is a 3708.dk test ║ 1 ║ ║ this is another test ║ 0 ║ ╚════════════════════════╩═══════╝
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;