MS SQL Server - Trying to mimic a regex inside a WHERE clause

Author

Trying to mimic a regex inside a WHERE clause

Jake

2006-03-15, 11:23 am

I have a case where I need to do regular expression-style matching on
rows in my database (we are using MS SQL Server 2000 SP4). The
statement I've been given to code goes as follows:

Count the number of "R" or "M" characters at the end of the invoice
number. Find all other invoice numbers in the database that have the
same base invoice number (the part before any "R" or "M" characters)
but a FEWER COUNT of "R" or "M" characters.

So, let me set up a simple example.

Invoice number: 2145RRR

We have THREE (3) M or R characters inside the invoice number, so we
want to try and find any other invoices in the database that have a
total of 0-2 R/M's in it.

A regular expression could take care of this, like this:

2145[RM]{0,2}$

The base number exists, followed by 0 to 2 matches of R or M (as long
as it's at the end of the string).

Possible values along with if they should be returned or not:

ROW 1: 2145 less: same base number (2145) but less R/Ms
ROW 2: 2145R less: only 1 match found
ROW 3: 2145M less: only 1 match found
ROW 4: 2145RM less: only 2 matches found
ROW 5: 2145MR less: only 2 matches found
ROW 6: 2145X no match: base invoice number (without R/Ms) is not
"2145".
ROW 7: 21456 no match: base invoice number (without R/Ms) is not
"2145".
ROW 8: 2145RMRR more: 4 matches found
ROW 9: 2145RRMR more: 4 matches found
ROW 10: 2145RMR same: 3 matches found
ROW 11: 2145MMR same: 3 matches found
ROW 12: 2145RRR same: 3 matches found

In the end, I would expect to get ROWS 1-5 back.

However, I'm not entirely sure how to do this in T-SQL.

So, is there any way to do this inline, right inside a query?

SELECT *
FROM Invoice
WHERE invoicenumber [insert stuff here]

Jake

2006-03-15, 11:23 am

Welp, nothing like having to explain your situation to help you figure
out the solution.

I ended up using the "xp_pcre" stored procedures found at this url:

http://www.thecodeproject.com/database/xp_pcre.asp

With everything installed, now my queries look like this:

declare @baseinvoicenumber varchar(50)
set @baseinvocenumber = '2145'

declare @numcharacters tinyint
set @numcharacters = 3

declare @regex varchar(50)
set @regex = '' + @baseinvoicenumber + '[RM]{0,' +
cast(@numcharacters-1 AS varchar) + '}$'

SELECT DISTINCT invoicenumber
FROM Invoice
WHERE master.dbo. fn_pcre_match(invoic
enumber, @regex) = 1

Sorry to bother everyone :)

Sponsored Links