> 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@turnstep.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
