SQL Server Query containing Regex

Question

I need to run a query against my SQL Server database using the below Regular Expression. This database exists on a development server, and I do not have access to enable CRL integration or create assemblies. Is there any other way to run a query that contains a Regular Expression against a SQL Server database?

If it is not possible, is it alternatively possible to formulate the below expression into a SQL Query?

The best solution I can come up with is to pull the table into a datatable using C#, do the necessary regex operations in C#, and then put it back into the database, but I'd really like to avoid that.

The regex: \d{4}(?=\D|$)(?<=(\D|^)\d{4}

EDIT: The SQL Query would need to take a string of any size with any alphanumeric characters in it and return an exactly 4 digit number. If there is a 5 digit number, it should not return 4 digits of that number.

Test Cases:

lis:xyz1257jd7=-1=-
&&12$*him1763&&whatwho
12344567xx1234xy000000
1874@45 &87 9876 abc123

Returns:

1.1257

2.1763

3.1234

4.9876

what is the actual query you are trying to execute against the RegEx which appears to be validating Phone numbers.. please provide more information... I think that you should do a bit more googling on your end.. here is a good link to look at.. msdn.microsoft.com/en-us/magazine/cc163473.aspx — MethodMan
– MethodMan, Commented Jan 22, 2015 at 21:06
Who needs Regex when you have the power of PATINDEX() and SUBSTRING() right at your fingertips? — Tab Alleman
– Tab Alleman, Commented Jan 22, 2015 at 21:07
@MethodMan Yes, I've read that article. In fact, it was the first in my google search and the first that I read. If you read my post, I specifically state that I cannot use CLR Integration, which is what is described in the article. I added more information about the query — TimidObserver
– TimidObserver, Commented Jan 22, 2015 at 21:18
@Tonci Performance is of no concern at all. I haven't been able to figure out a way with PatIndex or Substring that would satisfy my test cases above. I welcome any examples. — TimidObserver
– TimidObserver, Commented Jan 22, 2015 at 21:25
@TabAlleman Among the things I've tested, are the things you've listed. Lack of success led to my question here. Feel free to provide an example/answer. — TimidObserver
– TimidObserver, Commented Jan 22, 2015 at 21:43

RubberChickenLeader · Accepted Answer · 2015-01-22 22:22:21Z

4

Well for your specific test cases the following works

WITH Data AS (SELECT *
               FROM (VALUES 
('lis:xyz1257jd7=-1=-'),

('&&12$*him1763&&whatwho'),

('12344567xx1234xy000000'),

('56913546xx123xy000000'),

('1874@45 &87 9876 abc123'))x(val))

SELECT SUBSTRING (val,PATINDEX('%[^0-9][0-9][0-9][0-9][0-9][^0-9]%',val)+1,4)
  FROM Data
 WHERE PATINDEX('%[^0-9][0-9][0-9][0-9][0-9][^0-9]%',val) > 0

PATINDEX has a very limited regex functionality and as long as you can work with in its bounds its incredibly useful.

It can match characters [] Not match characters [^] and use the wild cards _ for a single character and % for many characters. The syntax inside the [] is the same as most regexes IE. [0-9] is 0 through 9 and [^0-9] is any character that is not 0 through 9.

Then you need to use substring to actually get any string back.

EDIT: As Pointed out by Tonci there was a small error. Adding a where clause to catch a PATINDEX of 0 fixes the issue, WHERE PATINDEX('%[^0-9][0-9][0-9][0-9][0-9][^0-9]%',val) > 0

edited Jan 22, 2015 at 22:22

answered Jan 22, 2015 at 21:55

RubberChickenLeader

1,0119 silver badges27 bronze badges

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

6 Comments

Tonci Over a year ago

If no match is found, this returns first 4 characters of the string.

RubberChickenLeader Over a year ago

@Tonci Yep I forgot PATINDEX returned a 0 if it was not found. The where clause will catch that case.

TimidObserver Over a year ago

This works very well except for the situation where no 4 digit number exists, which I should have included in my test cases.

RubberChickenLeader Over a year ago

@user3087977 See the edit. I added a where clause to take care of the case where a 4 digit string did not exist

TimidObserver Over a year ago

There is one other scenario that will not work. If the 4 digit number is at the end of the string it will not work. So using your example ('lis:xyz127jd7=-1=-1234') would not find 1234.

|

Collectives™ on Stack Overflow

SQL Server Query containing Regex

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related