How to use like clause in MySQL 5.0 Statement

问题:

I have a problem using 'like' clause in MySQL 5.0 I have written a stored procedure in MySQL 5.0 and calling the Stored Procedure from my Java Program the stored procedure below in the select statement i want to take the value of parameter departmentname for like clause please Help with this

    DROP PROCEDURE IF EXISTS upSelTests;
    DELIMITER //
    CREATE PROCEDURE upSelTests
    (
    IN departmentname varchar(50),
    IN testDateFrom varchar(10),
    IN testDateTo varchar(10)
    )
    BEGIN
    declare testDateFrom1 varchar(30);
    select PatientID,LabNo,arriveddate,RecieptNo,patientname,referredby,ID from
    tblAcceptTest where STR_TO_DATE(arriveddate, '%d/%m/%Y')>=STR_TO_DATE(testDateFrom,
    '%d/%m/%Y') and STR_TO_DATE(arriveddate, '%d/%m/%Y') <= STR_TO_DATE(testDateTo, 
    '%d/%m/%Y') and Status = 0 and DeptName like '%departmentname%';
    END //
    DELIMITER ;

回答1:

When you enclose departmentname in quotes, it becomes a character literal, and ceases to be a variable. Its value is ignored, and the literal '%departmentname%' is used instead.

The simplest solution is to use CONCAT to add '%' as a prefix and a suffix, like this:

and DeptName LIKE CONCAT( '%', departmentname, '%')

If, for example, departmentname is set to police, the result becomes

and DeptName LIKE '%police%'

回答2:

Format:

SELECT field1, field2,...fieldN table_name1, table_name2...
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'

E.g.:

SELECT * from tutorials_tbl 
 WHERE tutorial_author LIKE '%jay';