CODEWITHSUNDEEP
mysql
Wine Too
Wine Too

Reputation: 4655

MySql, search with LIKE %str%

I search my table with query contains LIKE clause %str%.

Is here a way to know where string 'str' was finded in sentence?

I would like to print out 'str' as markup (bold).

For this I need information where exact 'str' begins in any row which contain 'str'.

Upvotes: 0

Views: 226

Answers (2)

jakraska
jakraska

Reputation: 749

you can get the string position using the POSITION function ( http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_position ) however thats probably not the best way to do it since if they use the markup more than once it will only return the first position. It would be easier just to replace the string with the string wrapped with whatever markup you want.

If you want an all MySQL solution this would probably work:

SELECT REPLACE(exampleTable.field, 'search_string', '<b>search_string</b>')
FROM exampleTable
WHERE exampleTable.field LIKE '%search_string%';

However i would recommend doing any replacement like this on the PHP / ASP side... using string replacement tools from the respective language.

Upvotes: 2

Umbrella
Umbrella

Reputation: 4788

Sure, you want INSTR() . You could also use it in your where clause, though you'd want to compare performance between that and LIKE

SELECT INSTR(`field`, 'str') FROM `table` WHERE 0 < INSTR(`field`, 'str')

Remember that INSTR() returns a 1-based index, that is, the first character is postion 1, not position 0; saving 0 for "not found".

Upvotes: 2

Related Questions