SQL Like Statement Problems

SQL like statement problems

mysqli_stmt::bind_param can only bind a specific variable, not an expression. The supplied variable is passed to 'bind' by reference, and not by value, meaning that the underlying SQL gets whatever value that variable has at the time the command is executed, not when it's bound.

Use:

WHERE field LIKE CONCAT('%', ?, '%")

or do:

$brand = '%' . $brand . '%'

immediately before the command is executed.

What you can't do is:

WHERE field LIKE '%?%

because the ? bound variable must correspond to a single string or numeric value, not to a substring (or field name).

EDIT in this case, your real problem appears to be mixing up prepared statements (as supported by mysqli::prepare and mysqli_stmt::execute()) with plain old queries (as done with mysqli::query()). You should also just ask for the number of rows directly from the DB server, rather than pull the data and use num_rows:

$countQuery = "SELECT COUNT(ARTICLE_NO) FROM AUCTIONS WHERE upper(ARTICLE_NAME) LIKE ?";
if ($numRecords = $con->prepare($countQuery)) {
$numRecords->bind_param("s", $brand);
$numRecords->execute();
$numRecords->bind_result($num_rows);
$numRecords->fetch();
$numRecords->free_result();
$numRecords->close();
$last = ceil($rowcount/$page_rows);
} else {
print_r($con->error);
}

Problems with LIKE command in SQL Server

Query clauses like where colname like '%something%' are not able to take advantage of indexes and usually result in a full scan of the possible rows to ascertain which ones should be delivered

Although, as ChrisC points out in a comment, it's somewhat surprising that the more efficient clauses aren't firt used to reduce the candidate rowset down to a manageable size before trying to use like - perhaps the statistics for the table are not up to date enough for the query analysis to decide this - best run whatever counts for an explain query under SQL Server.

The reason your non-like query is so fast is because it almost certainly has an index on MgmtLogHost and/or MgmtLogTime which can be used to quickly cull unneeded rows.

One way you can fix this is to use something like insert/update triggers to process the MgmtLogText data only when changed, to extract the application names out and put them in a separate table which can be far better optimised.

Even just using such a trigger to keep a lowercased version of the column (in another column) would be an improvement. Using a case-insensitive collation means that selects run slower since they have to allow for XYZZY and xyzzy being classed as equal. If instead you maintain a lower-cased version in the table and ensure the check is done against lower case, that effort disappears as you only have one case to worry about.

And, by doing all this in the trigger, you ensure that it's only done when necessary (when the data is changed), not every time you want to select. This amortises the cost over many selects.

You can also use something like full text indexing if your DBMS supports it but I've often thought that was like trying to kill mosquitos with a thermo-nuclear warhead.

Yes, there are situations where you may need full text indexing but, in the vast majority of cases, you can gain efficiency by being a little more selective.

LIKE clause is not working in SQL server

You get an empty result because there is no row in your table where DocID is like both %DSRB% and %DHA% at the same time. You don't say in your question, but I guess you are expecting to receive the two rows with DocIds DHA12 and DSRB23.

To do this, you need to select rows where DocID is like either %DSRB% or %DHA%. Try changing the AND in your WHERE clause to an OR:

SELECT DocId, NSN, Qty, RequestDate, ReceiveDate
FROM Orders
WHERE (DocID LIKE '%DSRB%') OR (DocID LIKE '%DHA%')

See this introduction to SQL Logical Operators if you want more examples.

SQL Not Like Statement not working

If WPP.COMMENT contains NULL, the condition will not match.

This query:

SELECT  1
WHERE NULL NOT LIKE '%test%'

will return nothing.

On a NULL column, both LIKE and NOT LIKE against any search string will return NULL.

Could you please post relevant values of a row which in your opinion should be returned but it isn't?

SQL LIKE operator not showing any result when it should

Promoted to an answer as requested.

You have some space characters in the end of your Owner field in Vehicle table. '=' operator skips trailing spaces, 'like' operator doesn't.

In SQL. Why does this 'Like' statement with a wildcard not work?

This doesn't work because the brackets in the string have a special function in a LIKE statement - items between the brackets constitute a set of values that the singular character at the specified position matches. Your original pattern looks for a 1,0,4, or 2 followed by a 1 or an 0. To make this work, you should have a pattern like this:

Declare @Temp2 as nvarchar(max) = '[[]10004120][[]1100]'

SQL LIKE operator with aaa% having strange behavior

Please see this test case:

DECLARE @TestTable TABLE
(
id INT IDENTITY(1, 1)
, LastName NVARCHAR(255) COLLATE Danish_Norwegian_CI_AS
)

INSERT @TestTable (LastName)
VALUES (N'aa')
, (N'aaa')
, (N'aaaa')

SELECT *
FROM @TestTable AS TT
WHERE TT.LastName LIKE N'aaa%' COLLATE Latin1_General_CI_AS

Without the COLLATE Latin1_General_CI_AS you get the behaviour you are seeing.



Related Topics



Leave a reply



Submit