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 Commented Feb 27, 2019 at 20:47
  • sorry, only sql server 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. 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.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.