Adapting a REGEX for SQL

Question

I need to write a REGEX to exclude the following codes

D57 D59 D60 D61 D62X D63 D64 D69 D89 E85

and here is the chunk of SQL I have been using. There must be a better way by using extra brackets and the OR operator?

AND SUBSTRING(CODE, 1, 3) NOT LIKE 'D5[79]'
AND SUBSTRING(CODE, 1, 3) NOT LIKE 'D6[0-49]'
AND SUBSTRING(CODE, 1, 3) NOT IN ('D89','E85')

Which DBMS are you using? Oracle? Postgres? The LIKE operator does not support regular expressions. — user330315
– user330315, Commented Oct 24, 2013 at 16:27
The answer depends on your database engine, something you failed to specify. — Dan Bracuk
– Dan Bracuk, Commented Oct 24, 2013 at 16:27
@a_horse_with_no_name I cannot get to the config manager to look up the engine details but here is the spec from the About menu. Does this help?Microsoft SQL Server Management Studio 10.50.1600.1 Microsoft Analysis Services Client Tools 10.50.1600.1 Microsoft Data Access Components (MDAC) 6.1.7601.17514 Microsoft MSXML 3.0 6.0 Microsoft Internet Explorer 9.0.8112.16421 Microsoft .NET Framework 2.0.50727.5472 Operating System 6.1.7601 — stonypaul
– stonypaul, Commented Oct 24, 2013 at 16:44
Those are all client tools, not the actual DBMS you are using. But I would assume you are using Microsoft SQL Server as the DBMS — user330315
– user330315, Commented Oct 24, 2013 at 16:58
indeed seems to be Microsoft SQL Server... for LIKE you can use just a few metachars, take a look here: technet.microsoft.com/en-us/library/ms179859.aspx — Fawix
– Fawix, Commented Oct 24, 2013 at 17:12

Derrick Bell · Accepted Answer · 2013-10-24 17:50:49Z

1

If you DBA allows SQLCLR you can add a function to perform REGEX You can check out: Use-RegEx-in-SQL-with-CLR-Procs.

You can equally use TSQL to parse and exclude the codes. To parse the codes you can use any variety of string splitters. Check out: Split strings the right way – or the next best way In my example I created a table with codes then used the splitter function with a NOT IN to exclude the codes.

--create Table
CREATE TABLE [dbo].[Codes](
    [ID] [int] NULL,
    [Item] [varchar](6) NULL
) ON [PRIMARY]

--insert values
INSERT INTO [dbo].[Codes]([ID], [Item])
SELECT 1, N'A6' UNION ALL
SELECT 2, N'D60' UNION ALL
SELECT 3, N'G56' UNION ALL
SELECT 4, N'A34' UNION ALL
SELECT 5, N'D61'

--setup test
DECLARE @inString VARCHAR(100)

SET @inString = 'D57 D59 D60 D61 D62X D63 D64 D69 D89 E85'

SELECT *
    FROM Codes
    WHERE item NOT IN (
            SELECT Item
            FROM dbo.SplitStrings_Moden(@inString, N' ')
            )

answered Oct 24, 2013 at 17:50

Derrick Bell

2,7173 gold badges27 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

stonypaul Over a year ago

@Renegin OK thanks for that, I'll give it a go. However I am considering just using a NOT IN () clause and explicitly listing the codes.

Collectives™ on Stack Overflow

Adapting a REGEX for SQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related