From: | <btober(at)computer(dot)org> |
---|---|
To: | <greg(at)turnstep(dot)com> |
Cc: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: SUBSTRING for a regular expression |
Date: | 2004-07-07 12:38:17 |
Message-ID: | 4269.192.168.123.254.1089203897.squirrel@$HOSTNAME |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> SELECT
> SUBSTRING(description FROM '(.*), \\d{1,3},') AS vname,
> SUBSTRING(description FROM '.*, (\\d{1,3}),') AS age,
> SUBSTRING(description FROM '\\d{1,3}, of (.*?),? was charged')
> AS address,
> SUBSTRING(description FROM ' was charged ([^ ]+)') AS dow,
> SUBSTRING(description FROM ' was charged [^ ]+ with (.+)') AS charge
> FROM police_log;
>
> - --
> Greg Sabino Mullane greg(at)turnstep(dot)com
> PGP Key: 0x14964AC8 200407062103
Thanks Greg,
I was hopeful that this would work, since I had missed the need to double
the back-slash escape character in my original work, but something still
isn't right.
First I got an error message that psql didn't like the "?" characters in
the RE, so I eliminated them and wrote
SELECT
SUBSTRING(description FROM '(.*), \\d{1,3},') AS vname,
SUBSTRING(description FROM '.*, (\\d{1,3}),') AS age,
SUBSTRING(description FROM '\\d{1,3}, of (.*), was charged') AS address,
SUBSTRING(description FROM ' was charged ([^ ]+)') AS dow,
SUBSTRING(description FROM ' was charged [^ ]+ with (.+)') AS charge
FROM police_log;
This modified version of your suggestion gets the dow, and charge columns
right, but I'm still not seeing the vname, age, and address columns --
they return null. You've gotten me part way there, and I appreciate that.
Any further ideas?
--Berend Tober
From | Date | Subject | |
---|---|---|---|
Next Message | nobody | 2004-07-07 13:01:57 | win32 port |
Previous Message | Jerry LeVan | 2004-07-07 12:12:01 | Re: Loading Binary Data into Bytea Fields? |