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):

SE开发者_如何学CLECT 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?

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

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