Re: How to extract a substring using Regex

On Aug 23, 2007, at 21:08 , Postgres User wrote:

> You're right, that was a typo, I didn't copy and paste.
> I found the problem, I was using 2 forward slashes instead of a
> backslash + forward slash when pattern matching. The correct regex to
> extract my substring:
>
> substring(data_field from '<name>(.+)<\/name>')

I don't think the backslash is actually doing anything, (though two
forward slashes would definitely affect your result) as the slash
doesn't have a special meaning in the regexp.

test=# select substring('address city here <name>Rogers, Jim</name>
zip code place' from '<name>(.+)<\/name>');
substring
-------------
Rogers, Jim
(1 row)

test=# select substring('address city here <name>Rogers, Jim</name>
zip code place' from '<name>(.+)</name>');
substring
-------------
Rogers, Jim
(1 row)

Some scripting languages that use slashes to delimit regular
expressions, and therefore require slashes to be escaped, because
otherwise the slash would prematurely end the regexp.

In past versions of PostgreSQL, a backslash was used to escape single
quotes and enter other characters (e.g., \n). This is contrary to the
SQL spec, so you can now turn off this behavior by turning on
standard_conforming_strings. You'll see warnings if you use a
backslash in 8.2 with standard_conforming_strings off.

test=# show standard_conforming_strings;
standard_conforming_strings
-----------------------------
off
(1 row)

test=# select substring('address city here <name>Rogers, Jim</name>
zip code place' from '<name>(.+)<\/name>');
WARNING: nonstandard use of escape in a string literal
LINE 1: ...ere <name>Rogers, Jim</name> zip code place' from '<name>(.
+...
^
HINT: Use the escape string syntax for escapes, e.g., E'\r\n'.
substring
-------------
Rogers, Jim
(1 row)

Michael Glaesemann
grzm seespotcode net