postgresql regular expression and substring

Question

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?

Wolph · Accepted Answer · 2011-02-14 13:52:55Z

1

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

answered Feb 14, 2011 at 13:52

Wolph

80.4k12 gold badges142 silver badges152 bronze badges

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

2 Comments

Wolph Over a year ago

@helle: can you give an example of how infos looks like? Cause it should work with the given expression (works with all testdata I've given it)

helle Over a year ago

infos contains a JSON string.

Gavin · Accepted Answer · 2011-02-14 15:57:21Z

0

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

answered Feb 14, 2011 at 15:57

Gavin

6,5603 gold badges28 silver badges25 bronze badges

Collectives™ on Stack Overflow

postgresql regular expression and substring

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related