Select rows in SQL with regex values that match input?

Question

Rather than selecting rows based on whether their string value equals a given regular expression input, I want to select rows with regular expressions that match a given string input.

As far as purpose, I'm am trying to identify website names from input URLs.

TABLE
WEBSITE                    REGEX 
The New York Times         ^.+\.nytimes.com.*$

Is there a good way to do this? I'm using postgres, and I was hoping to avoid large loops.

Thanks!

From what I can tell, you should just be able to do SELECT * FROM TABLE where 'http://www.nytimes.com/foo/' ~ REGEX - Though I admit I haven't actually tried it. — Mike Christensen
– Mike Christensen, Commented Jan 24, 2014 at 18:45
A simple google search with "postgres regex" shows several results explaining how to do that already among the first few results. — FrankPl
– FrankPl, Commented Jan 24, 2014 at 18:46

Mike Christensen · Accepted Answer · 2014-01-24 18:56:23Z

1

This seems to work fine:

CREATE TABLE Sites
(
   SiteName text,
   RegEx text  
);

INSERT INTO Sites VALUES ('NY Times', '^.+\.nytimes.com.*$');

Then you can do:

SELECT * FROM Sites
WHERE 'http://www.nytimes.com/Foo' ~ RegEx;

Fiddle

Keep in mind this might start to get slow if you had a lot of rows, as it's going to have to do a sequential table scan each time and run the regular expression against each row. A better approach might be to parse the URL first and normalize it in some way, then look for an exact match in the table.

edited Jan 24, 2014 at 18:56

answered Jan 24, 2014 at 18:48

Mike Christensen

91.8k52 gold badges223 silver badges349 bronze badges

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

3 Comments

Bill_Flanders Over a year ago

I would likely have between 1,000 and 1,500 rows. I need the select output to appear very quickly to end user - it will be initiated via ajax. Do you think this would be "laggy" from UX point of view?

Mike Christensen Over a year ago

With 1,000 - 1,500? Probably not too bad, computers are pretty fast these days and you can probably wrap it in an IMMUTABLE or STABLE function so everything could be cached.

theEUG Over a year ago

What would be the MS SQL equivalent of this? I can't find anything related to SQL Server 2012

Collectives™ on Stack Overflow

Select rows in SQL with regex values that match input?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related