0

hope that you are doing fine

I am having very hard time writing a query

Here is my question explained

i have a database table say "jreviews_content" which has a field named "jr_produits"

In "jr_produits" the data is is the format *ryan-decosta*tom-gosling* so i want a search query that is exact word based i.e if the user type "rya" the mysql should not return anything but if the user type ryan then it should return the row likewise if the user type "gos" the mysql should not return anything but if the user type gosling then it should return the row where ryan and gosling are the exact words

the query that i am writing are

SELECT *
FROM `jreviews_content`
WHERE jr_produits LIKE '%*ryan-%' or jr_produits LIKE '%-ryan*%' 
or jr_produits LIKE '%*ryan*%' or jr_produits LIKE '%-ryan-%';

I want that to be done in some other way that is more efficient(either by regular expression or any other method)

SELECT * FROM `jreviews_content` WHERE jr_produits REGEXP '^[*-]ryan[*-]$'

It doen't fetch anything

neither does

SELECT * FROM `jreviews_content` WHERE jr_produits like '%[*-]ryan[*-]%'

Please suggest something

Guerra
  • 2,792
  • 1
  • 22
  • 32
Ryan decosta
  • 455
  • 1
  • 4
  • 10

1 Answers1

1

Try the MySQL regex word boundary markers. They're documented about halfway down this page:

SELECT *
FROM jreviews_content
WHERE jr_produits REGEXP '[[:<:]]ryan[[:>:]]'

Note that I don't have MySQL access today, so this is untested.

Also heed what @user1032531 said. Records with values like *ryan-decosta*tom-gosling* almost always mean "bad design".

Ed Gibbs
  • 25,924
  • 4
  • 46
  • 69
  • can you please explain the regular expression you wrote – Ryan decosta Oct 01 '13 at 12:30
  • this is not my table structure i am using JReviews component of joomla that has this structure to store the products – Ryan decosta Oct 01 '13 at 12:32
  • 1
    Fair enough on the table structure, and no worries. As for the regex, the `[[:<:]]` means "beginning of a word". It doesn't match to a character; it says "whatever follows (in this case `ryan`) must be at the beginning of a word". Likewise, the `[[:>:]]` means "end of a word". The link in my answer gives a little more detail. Hope this helps; it's kinda hard for me to explain :) – Ed Gibbs Oct 01 '13 at 12:37
  • hello its me again i have another query my site is in french language and hence there are lot of accent on my site for example frènch french i want the search to be acccent insensitive is there any way to do that – Ryan decosta Oct 09 '13 at 11:08
  • Hi Ryan - I Googled this and it may not be possible. See [here](http://stackoverflow.com/a/14137335/2091410). Still, I'd suggest posting this as a separate question because lots of people will see it and someone may know a workaround. Good luck :) – Ed Gibbs Oct 09 '13 at 18:29