SQL substring non greedy regex

Question

I have data like

http://www.linz.at/politik_verwaltung/32386.asp

stored in a text column. I thought a non-greedy extraction with

select substring(turl from '\..*?$') as ext from tdata

would give me .asp but instead it still ?greedely results in

 .linz.at/politik_verwaltung/32386.asp

How can I only match against the last occurence of dot .?
Using Postgresql 9.3

Could you provide expected output example?

Piotr Olaszewski
– Piotr Olaszewski

2015-02-20 07:58:02 +00:00
Commented Feb 20, 2015 at 7:58 — Piotr Olaszewski
– Piotr Olaszewski, Commented Feb 20, 2015 at 7:58
.asp is what you expect right

Brave Soul
– Brave Soul

2015-02-20 08:02:00 +00:00
Commented Feb 20, 2015 at 8:02 — Brave Soul
– Brave Soul, Commented Feb 20, 2015 at 8:02
Sorry for being imprecise, yes, .asp would be what I expect

JohnDoe
– JohnDoe

2015-02-20 12:51:30 +00:00
Commented Feb 20, 2015 at 12:51 — JohnDoe
– JohnDoe, Commented Feb 20, 2015 at 12:51

Antti Haapala · Accepted Answer · 2015-02-20 08:12:41Z

6

\.[^.]*$ matches . followed by any number of non-dot characters followed by end-of-string:

# select substring('http://www.linz.at/politik_verwaltung/32386.asp' 
  from '\.[^.]*$');
 substring 
-----------
 .asp
(1 row)

As for why the non-greedy quantifiers do not work here is that they still start matching as soon as possible while still trying to match as short as possible from there on.

answered Feb 20, 2015 at 8:12

Antti Haapala

135k23 gold badges298 silver badges349 bronze badges

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

Comments

VMAtm · Accepted Answer · 2015-02-20 08:14:15Z

2

Try this:

\.[\w]*$

Here is how it works:

all the word characters (\w), any numbers of them with *, between dot (\.) and the end of the string ($), with the last . itself.

Note: updated the answer, now will capture the strings ends with ..

edited Feb 20, 2015 at 8:14

answered Feb 20, 2015 at 8:05

VMAtm

28.4k17 gold badges85 silver badges133 bronze badges

5 Comments

mickmackusa Over a year ago

I don't see any good reason to wrap \w in a character class.

VMAtm Over a year ago

@mickmackusa this is a template, as one can use more specific set of chars inside

mickmackusa Over a year ago

but do you see how an unknowing user might assume that the square braces are necessary because you don't say that their optional?

VMAtm Over a year ago

@mickmackusa well, from my point of view, it's better to be more explicit with the code rather than use some implicit logic hidden from a beginner

mickmackusa Over a year ago

In that case, I should clarify the fact to readers that: the square braces serve no benefit in this pattern and can be safely removed. If anyone wants to extend the literal dot to be more characters, that should be wrapped in square braces too then escaping on the dot could be removed.

Collectives™ on Stack Overflow

SQL substring non greedy regex

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related