CODEWITHSUNDEEP
phpmysql
Ashwini Agarwal
Ashwini Agarwal

Reputation: 4858

MySql Search With Special Character

I searching records from database using MySql as - 

SELECT * FROM tab WHERE col LIKE '%myvalue%';

This is working fine and showing all the records having myvalue.

Now the problem is i have some records like my'value, myva'lue, myval-ue and my-value And also want to include these records in search. Also when i search for my'value then it should show myvalue and my-value.

How to achieve this? Any Help. Is it possible to do using LIKE operator?

Upvotes: 1

Views: 3937

Answers (4)

Ashwini Agarwal
Ashwini Agarwal

Reputation: 4858

Got this...

PHP Part

$term = str_replace('-','',str_replace(',', '', str_replace('\'', '', $term)));

MySql Part

SELECT *
FROM tab
WHERE ((REPLACE(REPLACE(REPLACE(col, '\'', ''), ',', ''), '-', '') LIKE "%$term%") 
OR (col LIKE "%$term%"));

See this demo.

Upvotes: 0

eisberg
eisberg

Reputation: 3771

As you are using PHP you should do the following, if someone uses your search:

  1. Get the search-term via $_POST
  2. Remove all special characters from the search-term (my'value becomes myvalue)
  3. Replace the search term with MySQL wildcards (myvalue becomes %m%y%v%a%l%u%e%)
  4. Execute SELECT * FROM tab WHERE col LIKE '%m%y%v%a%l%u%e%'

Any of the other solutions will not match all your requirements.

SQL Fiddle: http://sqlfiddle.com/#!2/20f811/2

Upvotes: 2

Fahim Parkar
Fahim Parkar

Reputation: 31627

How about this?

SELECT * FROM tab WHERE col LIKE 'my%' OR col LIKE '%value'

this will check col should start with my and end with value.

Demo

Edit 1

As there can be any special character and regexp don't work with MySQL, I would suggest you to do what you want to achieve in PHP by replacing special character by null and then matching the string with myvalue.

Hope this helps you.

Edit 2

I don't know php exactly, but can tell you what to do little bit.

mysql = "SELECT id, col FROM tab WHERE col LIKE '%my%' OR col LIKE '%value%'";
rs = mysql.execute();
String newString = "";
while (rs.next()) {
    newString = rs.getString(2);
    newString = new string after replacing special characters using regexp
    if (newString.equals("myvalue")) {
        // your code here..... this is place what you wanted.
    }
}

Upvotes: 0

GolezTrol
GolezTrol

Reputation: 116100

Use 

SELECT * FROM tab WHERE col LIKE '%myvalue%' OR col LIKE '%my_value%'

The underscore _ is a single character wildcard.

Upvotes: 1

Related Questions