0

I am trying to get the records in which "text" column contains a string strats from that letter or any word in that string starts from that letter.And it should work independent of accents. For example:

There are 4 records:

cafe
astocafe
bad café
good cafes

When the user types cafe it should display :

cafe
bad café
good cafes

I am trying to use this query:

SELECT id, texto FROM table WHERE CONVERT( `text` USING utf8 ) REGEXP '[[:<:]]cafe'

But its showing only:

cafe 
good cafes

its not matching accents.

user3350169
  • 209
  • 1
  • 4
  • 14
  • There are 5 records? – Barmar Dec 11 '14 at 08:08
  • Its dynamic i have given just an example.it may 4,or 5 or more – user3350169 Dec 11 '14 at 08:09
  • bacause 'é' is special character and you did not consider in your regex. – Yash Dec 11 '14 at 08:23
  • Try this regexp: '[[:<:]]caf[eé]' – ZuoLi Dec 11 '14 at 09:16
  • @Yash: `é` isn't a specail character, it's an accentuated letter. Like `èàêüù...`. – Toto Dec 11 '14 at 09:17
  • @ZuoLi thre may be accents like eéèêëEÉÈÊË or aáàâäAÀÁÂÃÄÅÆ] etc...i have to write every thing in query? – user3350169 Dec 11 '14 at 09:25
  • well, if your application is really multilanguage - then yes, you have. The second option is to store texto values in converted ways, adding the coumn like texto_simplified where to save strings in "normal" alphabet, but it can be costy in terms of db spase - so it depends on taks really. – ZuoLi Dec 11 '14 at 09:29

0 Answers0