1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Ignoring special characters in search query

Discussion in 'MySQL Forums' started by clem_c_rock, Oct 25, 2012.

  1. clem_c_rock

    clem_c_rock Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    I've been wracking my brain trying to find a solution for this for a couple of days. I'm trying to make a "smart" query that can handle a wide range of search terms. The queries run fine until there are special characters involved and I've had some success w/ the REPLACE method on some characters such as commas and dashes. Other characters such as quotes and ampersands will result in empty queries.

    Here's a few examples:

    the original name I'm searching for is "French Is Fun, Book 1 - 1 Year Option" and with this query below, I get results returned with these search terms:

    1. "French Is Fun"
    2. "French Is Fun, book"
    3. "French Is Fun, book"
    4. "French Is Fun, Book 1"

    Code (Text):

    SELECT * FROM `products` WHERE ( (LOWER(name) LIKE '%french is fun book%' OR
     LOWER(replace(name, '  ','')) LIKE '%french is fun book%' OR
     LOWER(replace(name, ' ','')) LIKE '%french is fun book%' OR
     LOWER(replace(name, '-','')) LIKE '%french is fun book%')
     
    However, when the original title has an ampersand in it like such: "Global History & Geography: The Growth of Civilizations - 1 Year Option" - I get an empty query when I try these different search terms:

    1. "Global History & Geography"
    2. "Global History Geography"

    I've tried this to no avail
    Code (Text):

    SELECT * FROM `products` WHERE  
        (LOWER(name) LIKE '%global history geograph%' OR  
            LOWER(replace(name, '  ','')) LIKE '%global history geography%' OR  
            LOWER(replace(name, ' ','')) LIKE '%global history geography%' OR
            LOWER(replace(name, ',','')) LIKE '%global history geography%' OR
            LOWER(replace(name, '&','')) LIKE '%global history geography%' OR  
            LOWER(replace(name, '-','')) LIKE '%global history geography%');
     
    I also tried adding an escape character to the ampersand and it doesn't help:
    Code (Text):

    SELECT * FROM `products` WHERE  
        (LOWER(name) LIKE '%global history geography%' OR  
            LOWER(replace(name, '  ','')) LIKE '%global history geography%' OR  
            LOWER(replace(name, ' ','')) LIKE '%global history geography%' OR
            LOWER(replace(name, ',','')) LIKE '%global history geography%' OR
            LOWER(replace(name, '\&','')) LIKE '%global history geography%' OR  
            LOWER(replace(name, '-','')) LIKE '%global history geography%');
     
    And commas in the name also return empty results. As a demonstration, the original name is this:

    "Amsco's AP Calculus AB/BC Preparing for the Advanced Placement Examinations - 1 Year Option"

    This attempt always returns empty queries:

    Code (Text):

    SELECT * FROM `products` WHERE
        ( (LOWER(name) LIKE '%amscos ap calculus%' OR
             LOWER(replace(name, ' ','')) LIKE '%amscos ap calculus%' OR
             LOWER(replace(name, '\'','')) LIKE '%amscos ap calculus%' OR
             LOWER(replace(name, ',','')) LIKE '%amscos ap calculus%' OR
             LOWER(replace(name, '-','')) LIKE '%amscos ap calculus%')
            ) AND ( (`products`.`type` = 'Rental' ) );
     
    Any ideas?
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    The & is chr(38) and the ' is chr(39) so you might try using those constructs in place of the actual characters in your where clause:

    Code (SQL):
    SQL> SELECT *
      2  FROM amp_ex
      3  WHERE LOWER(text) LIKE '%postro%' OR
      4  LOWER(REPLACE(text, chr(39), '')) LIKE '%postro%';
     
            ID TEXT                                     INS_DT    COMM
    ---------- ---------------------------------------- --------- --------------------
            12 Apostrophe TIME's here!                  25-OCT-12 Goo-yut!!!
            13 Apostrophe time'
    s here!                  25-OCT-12 Goo-yut!!!
            14 Apostrophe TIME's here!                  25-OCT-12 Goo-yut!!!
            15 Apostrophe time'
    s here!                  25-OCT-12 Goo-yut!!!
            16 Apostrophe TIME's here!                  25-OCT-12 Goo-yut!!!
            29 Apostrophe time'
    s here!                  25-OCT-12 Goo-yut!!!
            30 Apostrophe TIME's here!                  25-OCT-12 Goo-yut!!!
            31 Apostrophe time'
    s here!                  25-OCT-12 Goo-yut!!!
            32 Apostrophe TIME's here!                  25-OCT-12 Goo-yut!!!
            33 Apostrophe time'
    s here!                  25-OCT-12 Goo-yut!!!
            46 Apostrophe TIME's here!                  25-OCT-12 Goo-yut!!!
            47 Apostrophe time'
    s here!                  25-OCT-12 Goo-yut!!!
            48 Apostrophe TIME's here!                  25-OCT-12 Goo-yut!!!
            49 Apostrophe time'
    s here!                  25-OCT-12 Goo-yut!!!
            50 Apostrophe TIME's here!                  25-OCT-12 Goo-yut!!!
            63 Apostrophe time'
    s here!                  25-OCT-12 Goo-yut!!!
            64 Apostrophe TIME's here!                  25-OCT-12 Goo-yut!!!
            65 Apostrophe time'
    s here!                  25-OCT-12 Goo-yut!!!
            66 Apostrophe TIME's here!                  25-OCT-12 Goo-yut!!!
            67 Apostrophe time'
    s here!                  25-OCT-12 Goo-yut!!!
            80 Apostrophe TIME's here!                  25-OCT-12 Goo-yut!!!
            81 Apostrophe time'
    s here!                  25-OCT-12 Goo-yut!!!
            82 Apostrophe TIME's here!                  25-OCT-12 Goo-yut!!!
            83 Apostrophe time'
    s here!                  25-OCT-12 Goo-yut!!!
            84 Apostrophe TIME's here!                  25-OCT-12 Goo-yut!!!
            97 Apostrophe time'
    s here!                  25-OCT-12 Goo-yut!!!
            98 Apostrophe TIME's here!                  25-OCT-12 Goo-yut!!!
            99 Apostrophe time'
    s here!                  25-OCT-12 Goo-yut!!!
           100 Apostrophe TIME's here!                  25-OCT-12 Goo-yut!!!
     
    29 rows selected.
     
    SQL>
    SQL> select *
      2  from amp_ex
      3  where lower(text) like '
    %mpersan%' or
      4  lower(replace(text, chr(38), '
    ')) like '%mpersan%';
     
            ID TEXT                                     INS_DT    COMM
    ---------- ---------------------------------------- --------- --------------------
             1 No ampersand                             25-OCT-12 None
             2 No ampersand                             25-OCT-12 None
             3 No ampersand                             25-OCT-12 None
             4 Ampersand fun & games                    25-OCT-12 Boo-wah!!!
             5 Ampersand fun & games                    25-OCT-12 Boo-wah!!!
             6 Ampersand fun & games                    25-OCT-12 Boo-wah!!!
             7 Ampersand fun & games                    25-OCT-12 Boo-wah!!!
             8 Ampersand fun & games                    25-OCT-12 Boo-wah!!!
             9 Ampersand fun & games                    25-OCT-12 Boo-wah!!!
            10 Ampersand fun & games                    25-OCT-12 Boo-wah!!!
            11 Ampersand fun & games                    25-OCT-12 Boo-wah!!!
            17 Ampersand fun & games                    25-OCT-12 Boo-wah!!!
            18 No ampersand                             25-OCT-12 None
            19 No ampersand                             25-OCT-12 None
            20 No ampersand                             25-OCT-12 None
            21 Ampersand fun & games                    25-OCT-12 Boo-wah!!!
            22 Ampersand fun & games                    25-OCT-12 Boo-wah!!!
            23 Ampersand fun & games                    25-OCT-12 Boo-wah!!!
            24 Ampersand fun & games                    25-OCT-12 Boo-wah!!!
            25 Ampersand fun & games                    25-OCT-12 Boo-wah!!!
            26 Ampersand fun & games                    25-OCT-12 Boo-wah!!!
            27 Ampersand fun & games                    25-OCT-12 Boo-wah!!!
            28 Ampersand fun & games                    25-OCT-12 Boo-wah!!!
            34 Ampersand fun & games                    25-OCT-12 Boo-wah!!!
            35 No ampersand                             25-OCT-12 None
            36 No ampersand                             25-OCT-12 None
            37 No ampersand                             25-OCT-12 None
            38 Ampersand fun & games                    25-OCT-12 Boo-wah!!!
            39 Ampersand fun & games                    25-OCT-12 Boo-wah!!!
            40 Ampersand fun & games                    25-OCT-12 Boo-wah!!!
            41 Ampersand fun & games                    25-OCT-12 Boo-wah!!!
            42 Ampersand fun & games                    25-OCT-12 Boo-wah!!!
            43 Ampersand fun & games                    25-OCT-12 Boo-wah!!!
            44 Ampersand fun & games                    25-OCT-12 Boo-wah!!!
            45 Ampersand fun & games                    25-OCT-12 Boo-wah!!!
            51 Ampersand fun & games                    25-OCT-12 Boo-wah!!!
            52 No ampersand                             25-OCT-12 None
            53 No ampersand                             25-OCT-12 None
            54 No ampersand                             25-OCT-12 None
            55 Ampersand fun & games                    25-OCT-12 Boo-wah!!!
            56 Ampersand fun & games                    25-OCT-12 Boo-wah!!!
            57 Ampersand fun & games                    25-OCT-12 Boo-wah!!!
            58 Ampersand fun & games                    25-OCT-12 Boo-wah!!!
            59 Ampersand fun & games                    25-OCT-12 Boo-wah!!!
            60 Ampersand fun & games                    25-OCT-12 Boo-wah!!!
            61 Ampersand fun & games                    25-OCT-12 Boo-wah!!!
            62 Ampersand fun & games                    25-OCT-12 Boo-wah!!!
            68 Ampersand fun & games                    25-OCT-12 Boo-wah!!!
            69 No ampersand                             25-OCT-12 None
            70 No ampersand                             25-OCT-12 None
            71 No ampersand                             25-OCT-12 None
            72 Ampersand fun & games                    25-OCT-12 Boo-wah!!!
            73 Ampersand fun & games                    25-OCT-12 Boo-wah!!!
            74 Ampersand fun & games                    25-OCT-12 Boo-wah!!!
            75 Ampersand fun & games                    25-OCT-12 Boo-wah!!!
            76 Ampersand fun & games                    25-OCT-12 Boo-wah!!!
            77 Ampersand fun & games                    25-OCT-12 Boo-wah!!!
            78 Ampersand fun & games                    25-OCT-12 Boo-wah!!!
            79 Ampersand fun & games                    25-OCT-12 Boo-wah!!!
            85 Ampersand fun & games                    25-OCT-12 Boo-wah!!!
            86 No ampersand                             25-OCT-12 None
            87 No ampersand                             25-OCT-12 None
            88 No ampersand                             25-OCT-12 None
            89 Ampersand fun & games                    25-OCT-12 Boo-wah!!!
            90 Ampersand fun & games                    25-OCT-12 Boo-wah!!!
            91 Ampersand fun & games                    25-OCT-12 Boo-wah!!!
            92 Ampersand fun & games                    25-OCT-12 Boo-wah!!!
            93 Ampersand fun & games                    25-OCT-12 Boo-wah!!!
            94 Ampersand fun & games                    25-OCT-12 Boo-wah!!!
            95 Ampersand fun & games                    25-OCT-12 Boo-wah!!!
            96 Ampersand fun & games                    25-OCT-12 Boo-wah!!!
     
    71 rows selected.
     
    SQL>
     
  3. clem_c_rock

    clem_c_rock Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Dang - that looked so hopeful! I'm using mysql so my query looked like this:

    Code (SQL):
    SELECT * FROM `products` WHERE
        LOWER(name) LIKE '%Global History Geography%'OR
        LOWER(REPLACE(name, CHAR(38),''))
     LIKE '%global history geography%';
    Wonder why I'm getting an empty result?
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I'm using Mysql 5.5 and your query works:

    Code (SQL):
    mysql> CREATE TABLE products (name VARCHAR(150),
    -> TYPE VARCHAR(60),
    -> qty_on_hand INTEGER,
    -> inv_date datetime);
    Query OK, 0 ROWS affected (0.08 sec)
    mysql>
    mysql> INSERT INTO products(name, TYPE, qty_on_hand)
    -> VALUES ("Amsco's AP Calculus AB/BC Preparing for the Advanced Placement Examinations - 1 Year Option", "Rental", 99);
    Query OK, 1 ROW affected (0.01 sec)
    mysql>
    mysql> commit;
    Query OK, 0 ROWS affected (0.00 sec)
    mysql>
    mysql> SELECT * FROM `products` WHERE
    -> ( (LOWER(name) LIKE '%amscos ap calculus%' OR
    -> LOWER(REPLACE(name, ' ','')) LIKE '%amscos ap calculus%' OR
    -> LOWER(REPLACE(name, '\'','')) LIKE '%amscos ap calculus%' OR
    -> LOWER(REPLACE(name, ',','')) LIKE '%amscos ap calculus%' OR
    -> LOWER(REPLACE(name, '-','')) LIKE '%amscos ap calculus%')
    -> ) AND ( (`products`.`type` = 'Rental' ) );
    +---------------------------------------------------------------------------------------------+--------+-------------+----------+
    | name | TYPE | qty_on_hand | inv_date |
    +---------------------------------------------------------------------------------------------+--------+-------------+----------+
    | Amsco's AP Calculus AB/BC Preparing for the Advanced Placement Examinations - 1 Year Option | Rental | 99 | NULL |
    +---------------------------------------------------------------------------------------------+--------+-------------+----------+
    1 row in set (0.00 sec)
    mysql>
    I'm using the InnoDB engine -- that may make a difference.
     
  5. clem_c_rock

    clem_c_rock Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    zargon - you've been a great help. I've taken a few different approaches, and so far this version of my query is giving me the best results:

    Code (Text):

    SELECT * FROM `products` WHERE (in_store = 1 AND (LOWER( replace(replace(replace(replace(replace(name, ' ',''), ',', ''), '&', ''), '-', ''), ':', '') ) LIKE '%globalhistorygeography%' OR LOWER(name) LIKE '%global history geography%')
    )
     
    I'm still having some more "edge case queries that aren't returning results.

    For instance:

    This is the original title and if I use this query it returns results:

    "Lippincott's Q&A Review for NCLEX-RN, North American Edition"

    but, when I take out the ampersand, I don't get any results returned:

    "Lippincott's QA Review for NCLEX-RN, North American Edition"

    Thanks again, you've helped me get to the point where I'm getting acceptable results. Now it's down to knocking out the edge cases