CODEWITHSUNDEEP
mysqlsqlregexfull-text-searchpattern-matching
dev_musings
dev_musings

Reputation: 1181

MySQL String pattern matching - Alternatives for like

MySQL like clause lets wildcard searches like '%keyword%' where keyword is sandwiched within the column value.

For pattern matching where the keyword contains a part of the column value and when %keyword% will not work, we can use INSTR function to do the search.

Example:

Column="Apple"
$keyword = "An Apple a day"

Here, we cannot do Column like '%$keyword%' to make a match but (INSTR('$Keyword', Column)>0 would do the match.

What alternatives (regex?, fulltext search?, lucene?) do we have other than INSTR for pattern-matching such cases (examples?) ?

Update:

Column = "Golden Apple"
$keyword = "An Apple a day"

Even for samples like this, I would like to be able to match the $keyword with column as they have a common term "Apple".

Upvotes: 2

Views: 5234

Answers (3)

Marcus Adams
Marcus Adams

Reputation: 53840

Also consider SOUNDEX and Natural Language (using full text searches and relevance) searches.

Upvotes: 0

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115550

In this case, you can still use LIKE:

'%$keyword%' LIKE CONCAT('%', Column, '%')

Or regular expression:

'%$keyword%' REGEXP Column

Upvotes: 1

Raphaël VO
Raphaël VO

Reputation: 2640

I don't understand clearly what exactly you want to do, but try this link to find the help about pattern matching in mysql

Are you writing a procedure,a trigger, or simply a select?

Upvotes: 1

Related Questions