CODEWITHSUNDEEP
phpmysqlescapingcharactersql-like
Bilal075
Bilal075

Reputation: 85

MySQL escaping special characters

So, I have the following table:

ID  product
1   usb-stick
2   blackberry
3   cup of tea
4   usb-stick 2.0
5   computer

And wanted to achieve that whenever I look through my data with the LIKE condition, for example:

SELECT * FROM products WHERE product LIKE '% <value> %'

I would get the result I was searching for. But because there are special characters stored inside of my colums, I have to write down the actual value in order to have these back as a result.

So my question here is, if there is any way on how I could escape the special characters (which are stored inside of my colums, ex: usb-stick) in order to be searchable aswell for the following statement(s): 

ID  product         title / keyword (seperated by comma) product must be searchable for
1   usb-stick       usb stick, usb        stick (notice the spaces)
2   blackberry      blackberry, black berry
3   cup of tea      cup of tee
4   usb-stick 2.0   usb stick 2.0, usb stick 2, usb-stick 2, etc
5   computer        computer

In short, I just want the product to be searchable for other statements aswell (so not only the actual value / title).

Help would be appericiated!

Upvotes: 0

Views: 76

Answers (1)

Fabio Cardoso
Fabio Cardoso

Reputation: 1269

You can use replace() 

SELECT * FROM products WHERE replace(product , '-', '') LIKE '% <value> %'

Edit, this is not friendly at all but could work:

SELECT * FROM products WHERE
REPLACE(
    REPLACE(
        REPLACE(
            REPLACE(
                REPLACE(
                    REPLACE(
                        REPLACE(
                            REPLACE(
                                REPLACE(
                                    REPLACE(
                                        REPLACE(
                                            REPLACE(
                                                REPLACE(
                                                    REPLACE(
                                                        REPLACE(
                                                            REPLACE(
                                                                REPLACE(
                                                                    REPLACE(
                                                                        REPLACE(
                                                                            REPLACE(
                                                                                REPLACE(
                                                                                    REPLACE(
                                                                                        REPLACE(
                                                                                            REPLACE(
                                                                                                REPLACE(
                                                                                                    REPLACE(
                                                                REPLACE(
                                                                    REPLACE(
                                                                        REPLACE(
                                                                            REPLACE(
                                                                                REPLACE(
                                                                                    REPLACE(
                                                                                        REPLACE(product, '\"', ''),
                                                                                    '.', ''),
                                                                                '?', ''),
                                                                            '`', ''),
                                                                        '<', ''),
                                                                    '=', ''),
                                                                '{', ''),
                                                                                                    '}', ''),
                                                                                                '[', ''),
                                                                                            ']', ''),
                                                                                        '|', ''),
                                                                                    '\'', ''),
                                                                                ':', ''),
                                                                            ';', ''),
                                                                        '~', ''),
                                                                    '!', ''),
                                                                '@', ''),
                                                            '#', ''),
                                                        '$', ''),
                                                    '%', ''),
                                                '^', ''),
                                            '&', ''),
                                        '*', ''),
                                    '_', ''),
                                '+', ''),
                            ',', ''),
                        '/', ''),
                    '(', ''),
                ')', ''),
            '-', ''),
        '>', ''),
    ' ', '-'),
'--', '-') like '% <value> %'

Upvotes: 1

Related Questions