Sql-server – SQL regex to identify alphanumeric values

regexsql server

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.

Best Answer

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;

Related Solutions

Sql-server – SQL Server 2012 Exclude a value

If my understanding of your question is correct and you are only wanting to return records from tbloffer if there are also records in tblRecord matching those buyer ids, then you should be able to add a clause within your join so that it will only return those records.

I think this will cover it for you.

SELECT  COUNT(DISTINCT r.RecordID) AS Lines
      , COUNT(r.RecordID) AS Offers
      , MONTH(r1.Submitdate) AS M
      , DAY(r1.submitdate) AS D
FROM    tblReq r1
        INNER JOIN tblRecord r
            ON r1.ReqID = r.ReqID
        INNER JOIN tblOffer o
            ON r.RecordID = o.RecordID
            AND r.BuyerID = o.InsertedID
WHERE   r.BuyerID IN (36, 160499)
        AND o.OfferTypeID IN (1, 3)
        AND r1.Submitted = 1
        AND r1.SubmitDate >= '2015-07-01'
GROUP BY MONTH(r1.Submitdate)
      , DAY(r1.submitdate);

Hopefully I am understanding your question correctly and this is able to help you.

PostgreSQL – How to Use Character Classes in Regex SELECT

You can invert a character class with ^ as first character:

street_name ~ '\m21[^\d]'

Which is exactly the same as:

street_name ~ '\m21\D'

Or the long form:

street_name ~ '\m21[^[:digit:]]

Neither matches at the end of the string. I suggest two branches:

street_name ~ '\m21(\D|$)'

... which implements your requirements literally:

with a non-digit suffix or string terminator

Proper test tool

To check your requirements:

SELECT *
     , street_name ~ '\m21(\D|$)' AS erwin
     , street_name ~ '\m21\D*\M'  AS ruy  -- just without parentheses
FROM  (
   VALUES
   (1, '21'    , 'true'::bool)
 , (2, '21X'   , 't')
 , (3, '21 foo', 't')
 , (4, '21X11' , 't') -- !
 , (5, '121'   , 'f')
 , (6, '210'   , 'f')
 , (7, 'X21'   , 'f')
   ) t(id, street_name, should_match);

What about '21X11': digits *after a non-digit suffix?
My regexp accepts it, @Ruy's regexp rejects it.
As I read your question it should pass?

The (already inverted) class shorthand \D is not allowed inside another character classes. The manual:

Within bracket expressions, \d, \s, and \w lose their outer brackets, and \D, \S, and \W are illegal.

Bold emphasis mine.

You did not rule out 'X21' explicitly, but I assume you want to start with a word boundary like in your example: \m.

Best Answer

Related Solutions

Sql-server – SQL Server 2012 Exclude a value

PostgreSQL – How to Use Character Classes in Regex SELECT

Proper test tool

Related Question