2

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.

4
  • 1
    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. Commented Feb 27, 2019 at 20:41
  • always 4 numbers followed by a dot and then two uppercase letters
    – rg1105
    Commented Feb 27, 2019 at 20:47
  • sorry, only sql server
    – rg1105
    Commented Feb 27, 2019 at 20:48
  • 2
    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.
    – Hannah Vernon
    Commented Feb 27, 2019 at 20:56

1 Answer 1

3

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.