Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2010
    Posts
    7

    Unanswered: preprocess text before selecting text field

    I have a strange question. I have a normal text field (called plantname) in a table (Plants) that has plant names like this:

    Delphinium * Black Knight
    Delphinium * Red Flower 3
    Delphinium * Blue Orchid
    etc.

    I obviously can select the first one with:
    select * from Plants where plantname = 'Delphinium * Black Knight'

    Is it possible to get mysql to select on plantname but preprocess the text and apply a regex to it first? what I'm trying to do is make the above select also work like this:
    select * from Plants where plantname = 'delphinium-black-knight'

    So I would like mysql to pretend the only characters in plantname are A-Za-z0-9 and the spaces are a dash. I know I could just recreate a new field and copy the names over in the delphinium-black-knight format, but I'm trying not too.

    Any got a clue?
    Thanks.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what difference do you expect this 'pre processing' to achieve
    what isn't working as you'd expect or need
    is this a kludge workaround for a flaky physical design
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jul 2010
    Posts
    7
    ya, I'm going to create a new field. thats way to difficult otherwise.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    sorry
    I still don't understand what you are trying to achieve. I suspect you need to redesign the table but I'm not sure. However I am certain the storing what is essentially the same information in two columns definately is a symptom of a poor design
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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