Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2010
    Posts
    53

    Unanswered: retrieve id number from a string

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(event,']',1),'[',-1)) as id 
      FROM logs 
     WHERE LEFT(event,5) = 'Quote'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    whats stopping you putting the ID as a separate column?
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Apr 2010
    Posts
    53
    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,...

  5. #5
    Join Date
    Apr 2010
    Posts
    53
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •