Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2013
    Posts
    4

    Unanswered: Issues with replacing characters in Query

    The purpose of this query was to search through the database and remove /, -, and \ from part numbers in the event that the user failed to input the special characters within the part number field.

    SELECT
    tbl_shop.shop_desc,
    tbl_location.location_desc,
    tbl_inventory.location_id,
    tbl_inventory.shop_id,
    tbl_inventory.uid,
    tbl_inventory.part_number,
    tbl_inventory.model_number,
    tbl_inventory.serial_number,
    tbl_inventory.desc,
    tbl_inventory.date_modified
    FROM tbl_inventory
    INNER JOIN tbl_shop ON
    tbl_inventory.shop_id = tbl_shop.shop_id
    INNER JOIN tbl_location ON
    tbl_inventory.location_id = tbl_location.location_id
    WHERE
    replace(replace(replace(part_number,'-',''),'\',''),'/','') like '%6440005001%'

    This query, on MSSQL2008 returns the proper records.. however, when I ported my application over to MySql due to reasons beyond my control.. this function returns the following error:

    #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''' at line 18

    Line 18 is the replacement function(s) and if I were to remove the number(s) between '%%' the error returned is the following:

    #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%'' at line 18

    Notice on both errors there is an extra ' at the end..

    I'm not really understanding why this is happening and I'm hoping it's just my tired eyes looking at all this..

    Is anyone able to shed some light on this?


    Cancerion

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Im pretty certain you are hitting problems becuase of the \ in your replace. the \ is used as the escape character in MySQL to use a \ in a MySQL query you need to escape it
    Code:
    replace(replace(replace(part_number,'-',''),'\\',''),'/','') like '%6440005001%'
    you are probably better off using regular expressions
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2013
    Posts
    4

    Works, Thanks!

    healdem,

    Thank you! I'd been converting our php application all day and missed that.

    I actually needed three \'s for some reason.. Maybe because it's within the PHP blocks.. Maybe I needed to escape the escape character so it actually made it to the MySQL server. Not sure. But it's working and I thank you for your input.

    Either way, back to your mention of Regex's I had thought that as well originally but shied away from Regex for two reasons:

    1. The special characters can be anywhere in our string's within the database of part numbers (due to different part manufacturers) and I couldn't for the life of me figure out how to format the expression.
    2. I needed to produce a functional product within a reasonable amount of time and didn't have the time to research it.

    Being that this is the only (albeit most important) function within my PHP application that requires this SQL statement.. This works for now.

    Pretty soon after launch i'll be converting our database of parts to use an id system so that will be obsolete anyhow.

    Thank you again for your input. It's greatly appreciated.

    -cancerion.

Posting Permissions

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