Search code examples
mysqlsql-serverregext-sqlsql-like

using 'like' to find whole words in sql

I have a search routine that works in transact-sql (Microsoft's version), it does something to similar to:

search * from mytable
where ' ' + title + ' ' like ' %white% '

My goal is to find words but not subwords. I don't want to find "whitener", I just to find titles that contain white.

This kind of search does not work in MySQL though. MySQL has the 'like' operator, but something about it must be different.

So my question, is, is there a general way of doing this that works in both t-sql and MySQL.

Solution

  • Your T-SQL query won’t work as posted. I had to modify it to obtain the desired results:

    select * from mytable
where ' ' + title + ' ' like '% white %'

    Note the order of the spaces and percentage wildcards within the single quotes. Also it’s SELECT not SEARCH.

    To get it working in MySQL, the query needs to be rewritten as:

    select * from mytable
where concat(' ', title, ' ') like '% white %';

    MySQL uses the CONCAT() function to implement string concatenation while T-SQL traditionally uses plus sign, + to implement string concatenation.

    MS SQL server has supported the CONCAT function since SQL Server 2012 so if you’re using a recent version, this second query should be the only one you need. Otherwise, I don’t think there’s a simple version which will work with both types of DBMS.