CODEWITHSUNDEEP
sqlregexpostgresql
Bill_Flanders
Bill_Flanders

Reputation: 603

Select rows in SQL with regex values that match input?

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!

Upvotes: 4

Views: 3037

Answers (1)

Mike Christensen
Mike Christensen

Reputation: 91726

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.

Upvotes: 1

Related Questions