sql-like – Page 4 – Tarik Billa

How can I with mysqli make a query with LIKE and get all results?

March 31, 2023 by Tarik

Here’s how you properly fetch the result $param = “%{$_POST[‘user’]}%”; $stmt = $db->prepare(“SELECT id, username FROM users WHERE username LIKE ?”); $stmt->bind_param(“s”, $param); $stmt->execute(); $result = $stmt->get_result(); while ($row = $result->fetch_assoc()) { echo “Id: {$row[‘id’]}, Username: {$row[‘username’]}”; } or, if you prefer the old fetch and bind_result syntax, you can also do: $param = “%{$_POST[‘user’]}%”; … Read more

SQLite Like % and _

March 25, 2023 by Tarik

It is standard SQL that in LIKE expressions: % matches any sequence of characters, including an empty one. It is equivalent to .* in a regular expression. _ matches a single character. It is equivalent to . in a regular expression. You can choose a character for escaping %, _ and itself itself with: … … Read more

MySQL: NOT LIKE

March 13, 2023 by Tarik

categories_posts and categories_news start with substring ‘categories_’ then it is enough to check that developer_configurations_cms.cfg_name_unique starts with ‘categories’ instead of check if it contains the given substring. Translating all that into a query: SELECT * FROM developer_configurations_cms WHERE developer_configurations_cms.cat_id = ‘1’ AND developer_configurations_cms.cfg_variables LIKE ‘%parent_id=2%’ AND developer_configurations_cms.cfg_name_unique NOT LIKE ‘categories%’

SQL SELECT LIKE (Insensitive casing)

March 8, 2023 by Tarik

use LOWER Function in both (column and search word(s)). Doing it so, you assure that the even if in the query is something like %VaLuE%, it wont matter select qt.* from query_table qt where LOWER(column_name) LIKE LOWER(‘%vAlUe%’);

SQL query for a carriage return in a string and ultimately removing carriage return

March 6, 2023 by Tarik

this will be slow, but if it is a one time thing, try… select * from parameters where name like ‘%’+char(13)+’%’ or name like ‘%’+char(10)+’%’ Note that the ANSI SQL string concatenation operator is “||”, so it may need to be: select * from parameters where name like ‘%’ || char(13) || ‘%’ or name … Read more

how to use a like with a join in sql?

March 4, 2023 by Tarik

Using INSTR: SELECT * FROM TABLE a JOIN TABLE b ON INSTR(b.column, a.column) > 0 Using LIKE: SELECT * FROM TABLE a JOIN TABLE b ON b.column LIKE ‘%’+ a.column +’%’ Using LIKE, with CONCAT: SELECT * FROM TABLE a JOIN TABLE b ON b.column LIKE CONCAT(‘%’, a.column ,’%’) Mind that in all options, you’ll … Read more

How to escape underscore character in PATINDEX pattern argument?

February 19, 2023 by Tarik

I’ve always done it with brackets: ‘%[_]%’

Element or class LIKE selector for jQuery?

February 15, 2023 by Tarik

Using $(“[class^=main]”) will select all elements whose classname starts with ‘main’. Take a look at jQuery docs about selectors, there are a lot of other variations you can use, for example: [class*=main] will select elements whose classname contains ‘main’ [class~=main] will select elements whose classname has the word ‘main’ (delimited with spaces) [class$=main] will select … Read more

How to speed up SELECT .. LIKE queries in MySQL on multiple columns?

February 11, 2023 by Tarik

An index wouldn’t speed up the query, because for textual columns indexes work by indexing N characters starting from left. When you do LIKE ‘%text%’ it can’t use the index because there can be a variable number of characters before text. What you should be doing is not use a query like that at all. … Read more

Search for string within text column in MySQL

February 8, 2023 by Tarik

You could probably use the LIKE clause to do some simple string matching: SELECT * FROM items WHERE items.xml LIKE ‘%123456%’ If you need more advanced functionality, take a look at MySQL’s fulltext-search functions here: http://dev.mysql.com/doc/refman/5.1/en/fulltext-search.html