I need to retrieve a series of id number from a log table.

The log table has entries as follows:

log_id time event

1 2010-09-07 16:08:06 Quote sent to: customer_a [1176] cust_a@email.com

2 2010-09-07 16:08:06 Quote sent to: customer_b [1320] cust_b@email.com

The ids are in the square brackets and i need to retrieve a list of them (i know i should have designed my logs table better!)

I've come up with the following query:

select substr(`event`, locate('[', `event`)+1, locate(']', `event`) - (locate('[', `event`)+1)) as id from `logs` where substr(`event`,1,5) = 'Quote'

Which does work but seems a bit of a mess - i just wondered if there was a better solution?

any help/comments gratefully received

thanks

j

whats stopping you putting the ID as a separate column?

thanks rudy, that looks more elegant...

healdem, nothing really stopping putting the ID as a separate column - only its just a general log table and records all sorts of stuff so there isnt always a customer ID. hadnt thought i'd have to use it like this originally,...

sorry to bother you with this again...

I wont to join the result of this query back up with the details from my customer table and have attempted to do so with the following:

SELECT customer_id, name, last_contact_date, last_contact_date_unix last_order_date FROM customers WHERE customer_id IN
(SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(event,']',1), '[', -1) AS id
FROM logs
WHERE LEFT(event,5) = 'Quote')
AND
last_contact_date_unix < 1

however it just seems to hang the server - the first record returns about 2000 records and there's around 20000 in the customer table - is there a more efficient way of doing this? any ideas much appreciated.

ta

j