CODEWITHSUNDEEP
regexpostgresqlsubstring
helle
helle

Reputation: 11660

postgresql regular expression and substring

I have a regular expression http\:\/\/domainname\.com\/\S{4} which should catch urls like this: http://domainname.com/key4 in a longer text.

I want to get only the key and match it with a table field in my postgres database.

after trying some stuff I came to this query (for grabbing keys):

SELECT substring(infos FROM '[http\:\/\/domainname\.com\/\S{4}]{7}' ) AS key FROM actions

as a result i get the /domainname.com for each row ... well, no keys as you can see.

what am I doing wrong?

can anyone tell me what the {7} stands for?

Upvotes: 0

Views: 2478

Answers (2)

Gavin
Gavin

Reputation: 6490

This might be what you are looking for in this specific case

select substring(substring('long text [http://domainname.com/key4] more text',E'http\:\/\/domainname\.com\/\\S{4}'),E'\\S{4}$');

which will extract the key part if it is four characters long. If you might also be looking for something like key456 then a more generic match such as 

select substring(substring('long text [http://domainname.com/key467] more text',E'http\:\/\/domainname\.com\/key\\d+'),E'key\\d+$');

may be more appropriate

Upvotes: 0

Wolph
Wolph

Reputation: 80111

The {7} stands for 7 times the previous pattern. So in this case, the different characters between [ and ]. i.e. [abc]{3} matches aaa cba or any other combination.

I'm fairly certain that this is not what you want. You are probably looking for something like this instead:

SELECT substring('http://domainname.com/key4' from 'http://domainname\\.com/\\S{4}')
FROM actions

Upvotes: 1

Related Questions