Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2013
    Posts
    1

    Unanswered: SQL String Functions (removing from string)

    Hello,

    I have a MySQL database field 'product' which has several products listed as well as additional appended information concerning the product. The contents of the database field look similar to the following:

    1M96-3P - MR + Disc:$10
    1018 - MR + Disc:$10
    1M99-3P - MR + Disc:$10
    1M02-SS - SF + Disc:$15
    6504 - SR + Disc:$15
    23M9 - bulk + Apron
    23M9 - bulk + Apron
    20M1 - bag + Cruise
    Smartchoice 5G Smartbox-50#
    Smartchoice 5G Smartbox-50#
    Smartchoice 5G Smartbox-50#
    RoundUp Power Max - Bulk + Disc:$1


    What I would like to do is write a SQL UPDATE statement that takes the product field and deletes '+ Disc:' and everything to the right of the '+ Disc:' portion of the product name.

    I have been looking at and playing with SQL String Functions but I can't seem to find anything that works specifically how I am trying to do it.

    Thank you for your assistance

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Moved to MySQL forum
    George
    Home | Blog

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so thats going to be
    mycolumn = left(mycolumn, instr(mycolumn,' + disc')-1)

    where instr(mycolumn,' + disc') > 0

    or
    where mycolumn like '%+ disc%'
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Besides the design flaw (you should store separate pieces of information in to separate fields in your tables) you should look at the following functions in the manual:

    Locate()
    Left()
    George
    Home | Blog

Tags for this Thread

Posting Permissions

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