How to extract a substring using Regex

On Aug 23, 2007, at 19:33 , Postgres User wrote:

I'm new to Regex in Postgres. Can someone give me a quick pointer on
how I'd SELECT the substring between '<name>' and '</name>' in
a field?

Check out regexp_replace:

http://www.postgresql.org/docs/8.2/interactive/functions-
matching.html#FUNCTIONS-POSIX-REGEXP

One of the forms of substring might work for you, too.

Michael Glaesemann
grzm seespotcode net

Yes, I read the manual. I think I had a problem because of the
special chars (< / >) that I'm trying to search for... Still looking
for the right syntax.

[Please don't top post as it makes the discussion more difficult to
follow, and please reply to the list so that others may benefit from
and participate in the discussion.]

On Aug 23, 2007, at 19:49 , Postgres User wrote:

Yes, I read the manual. I think I had a problem because of the
special chars (< / >) that I'm trying to search for... Still looking
for the right syntax.

Why don't you show us what you've tried and the errors you're
getting? That way we can help you figure out what you're doing wrong
rather than just give you an answer.

Michael Glaesemann
grzm seespotcode net

Yes, I read the manual. I think I had a problem because of the
special chars (< / >) that I'm trying to search for... Still looking
for the right syntax.

Why don't you show us what you've tried and the errors you're
getting? That way we can help you figure out what you're doing wrong
rather than just give you an answer.

Michael Glaesemann

SELECT substring(data_field from '<name>(.)</name>')
FROM myTable

On Aug 23, 2007, at 20:01 , Postgres User wrote:

Yes, I read the manual. I think I had a problem because of the
special chars (< / >) that I'm trying to search for... Still
looking
for the right syntax.

Why don't you show us what you've tried and the errors you're
getting? That way we can help you figure out what you're doing wrong
rather than just give you an answer.

Michael Glaesemann

SELECT substring(data_field from '<name>(.)</name>')
FROM myTable

Looks like you might want to brush up on regular expressions in
general. Your expression will match a single character between the
<name> tags. You might want to try something like .+ instead.

Michael Glaesemann
grzm seespotcode net

On 8/23/07, Michael Glaesemann <grzm@seespotcode.net> wrote:

On Aug 23, 2007, at 20:01 , Postgres User wrote:

Yes, I read the manual. I think I had a problem because of the
special chars (< / >) that I'm trying to search for... Still
looking
for the right syntax.

Why don't you show us what you've tried and the errors you're
getting? That way we can help you figure out what you're doing wrong
rather than just give you an answer.

Michael Glaesemann

SELECT substring(data_field from '<name>(.)</name>')
FROM myTable

Looks like you might want to brush up on regular expressions in
general. Your expression will match a single character between the
<name> tags. You might want to try something like .+ instead.

Michael Glaesemann

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

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

On Thu, 2007-08-23 at 19:08 -0700, Postgres User wrote:

On 8/23/07, Michael Glaesemann <grzm@seespotcode.net> wrote:

On Aug 23, 2007, at 20:01 , Postgres User wrote:

Yes, I read the manual. I think I had a problem because of the
special chars (< / >) that I'm trying to search for... Still
looking
for the right syntax.

Why don't you show us what you've tried and the errors you're
getting? That way we can help you figure out what you're doing wrong
rather than just give you an answer.

Michael Glaesemann

SELECT substring(data_field from '<name>(.)</name>')
FROM myTable

Looks like you might want to brush up on regular expressions in
general. Your expression will match a single character between the
<name> tags. You might want to try something like .+ instead.

Michael Glaesemann

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

FWIW, I find the following site extremely useful when trying to create
moderately complex regular expressions: http://www.rexv.org.

HTH,

Mark.

--
ILande - Open Source Consultancy
http://www.ilande.co.uk

On Fri, 24 Aug 2007 06:31:58 +0100
Mark Cave-Ayland <mark.cave-ayland@ilande.co.uk> wrote:

substring(data_field from '<name>(.+)<\/name>')

FWIW, I find the following site extremely useful when trying to
create moderately complex regular expressions: http://www.rexv.org.

Nice site - here's another good one for an installed app that is
excellent for even advanced regexp's:

http://weitz.de/regex-coach/

It used to be cross-platform but now he only develops a Windows
version.

Thanks,

Josh

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
owner@postgresql.org] On Behalf Of Josh Trutwin
Sent: Friday, August 24, 2007 7:58 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] [OT - sorta] How to extract a substring using Regex

On Fri, 24 Aug 2007 06:31:58 +0100
Mark Cave-Ayland <mark.cave-ayland@ilande.co.uk> wrote:

substring(data_field from '<name>(.+)<\/name>')

FWIW, I find the following site extremely useful when trying to
create moderately complex regular expressions: http://www.rexv.org.

Nice site - here's another good one for an installed app that is
excellent for even advanced regexp's:

http://weitz.de/regex-coach/

It used to be cross-platform but now he only develops a Windows
version.

Thanks,

Josh

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

As long as we are talking about regex tools... I found this tool a while
back and today I don't know how I ever figured out regular expressions
without it. (http://www.regexbuddy.com/) Unfortunately it is not free, but
for the time that it saves, it is WELL worth $40 a copy. In fact, I
personally bought it and was going to charge back to my company, but decided
I would keep the licenses myself.
This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the sender. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Lee Keel wrote:

-----Original Message-----

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

As long as we are talking about regex tools... I found this tool a while
back and today I don't know how I ever figured out regular expressions
without it. (http://www.regexbuddy.com/) Unfortunately it is not free, but
for the time that it saves, it is WELL worth $40 a copy. In fact, I
personally bought it and was going to charge back to my company, but decided
I would keep the licenses myself.
This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the sender. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail.

Woah! That is great. Now to find a linux version.

Joshua D. Drake

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

- --

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997 http://www.commandprompt.com/
UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFGzvScATb/zqfZUUQRAnBRAKCcMesTYri0caOiNzShMwTXXw137ACfcLxu
3Egy0J00iFIaFFfl9KiOlc0=
=rlVZ
-----END PGP SIGNATURE-----

-----Original Message-----
From: Joshua D. Drake [mailto:jd@commandprompt.com]
Sent: Friday, August 24, 2007 10:09 AM
To: Lee Keel
Cc: Josh Trutwin; pgsql-general@postgresql.org
Subject: Re: [GENERAL] [OT - sorta] How to extract a substring using Regex

Woah! That is great. Now to find a linux version.

Joshua D. Drake

I am not sure if you saw the information about the installation on wine
(http://www.regexbuddy.com/wine.html). I am not a linux guru, so I really
couldn't speak to this very much. Sorry. But yes, very cool tool and will
help newbies and the more experienced.

-LK
This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the sender. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail.

Le Friday 24 August 2007 17:09:16 Joshua D. Drake, vous avez écrit :

As long as we are talking about regex tools... I found this tool a while
back and today I don't know how I ever figured out regular expressions
without it. (http://www.regexbuddy.com/)

Woah! That is great. Now to find a linux version.

Does this tool looks like the one you'd wanna find?
http://www.blackie.dk/KDE/KRegExpEditor/
http://docs.kde.org/stable/en/kdeutils/KRegExpEditor/

Regards,
--
dim