How about split it into four parts – [MyColumn] Like ‘% doc %’ OR [MyColumn] Like ‘% doc’ OR [MyColumn] Like ‘doc %’ OR [MyColumn] = ‘doc’ Edit: An alternate approach (only for ascii chars) could be: ‘#’+[MyColumn]+’#’ like ‘%[^a-z0-9]doc[^a-z0-9]%’ (You may want to take care of any special char as well) It doesn’t look … Read more
What would be useful here would be a LIKE ANY predicate as is available in PostgreSQL SELECT * FROM tbl WHERE my_col LIKE ANY (ARRAY[‘%val1%’, ‘%val2%’, ‘%val3%’, …]) Unfortunately, that syntax is not available in Oracle. You can expand the quantified comparison predicate using OR, however: SELECT * FROM tbl WHERE my_col LIKE ‘%val1%’ OR … Read more
First, the PreparedStatement placeholders (those ? things) are for column values only, not for table names, column names, SQL functions/clauses, etcetera. Better use String#format() instead. Second, you should not quote the placeholders like ‘?’, it would only malform the final query. The PreparedStatement setters already do the quoting (and escaping) job for you. Here’s the … Read more
Prepared statements protect you from sql injection, so sql code in the parameters will not be interpreted. You will have to build a sql query with the correct number of AND itemTitle LIKE ? before calling prepare(). $keywords = preg_split(‘/[\s]+/’, $keywords); $totalKeywords = count($keywords); $query = “SELECT * FROM prodsTable WHERE itemTitle LIKE ?”; for($i=1 … Read more
MySQL can use an index if your query looks like foo LIKE ‘abc%’ or foo LIKE ‘abc%def%’. It can use the index for any portion or the string before the first wildcard. If you need to match a word anywhere within a string, you might want to consider using FULLTEXT indexes instead. More detail on … Read more
order by case when name LIKE “%John%” then 1 when name LIKE “%Doe%” then 2 else 3 end
Do you just want to search on word boundaries? If so a crude version might be: SELECT * FROM products WHERE product_name LIKE “% foo %”; Or you could be a bit cleverer and look for word boundaries with the following REGEXP SELECT * FROM products WHERE product_name RLIKE “[[:<:]]foo[[:>:]]”;
A much better solution in terms of performance: SELECT …. FROM …. WHERE `concatenated` LIKE BINARY ‘%SearchTerm%’; String comparision is case-sensitive when any of the operands is a binary string. Another alternative is to use COLLATE, SELECT …. FROM …. WHERE `concatenated` like ‘%SearchTerm%’ COLLATE utf8_bin;
Try SELECT * FROM Table1 INNER JOIN Table2 ON Table1.col LIKE CONCAT(‘%’, Table2.col, ‘%’) MySQL does string concatenation differently from other databases, so in case you want to port your app, you need to have an alternate version where || is used as concatenation operator, as mentioned by Michael in another answer. This operator doesn’t … Read more
You could use the DATEPART() function SELECT * FROM record WHERE (DATEPART(yy, register_date) = 2009 AND DATEPART(mm, register_date) = 10 AND DATEPART(dd, register_date) = 10) I find this way easy to read, as it ignores the time component, and you don’t have to use the next day’s date to restrict your selection. You can go … Read more