Employing IF Statements with LIKE Operator in MySQL

To retrieve all records from the tutorials_tbl table where the author name ends with “jay,” one can use the LIKE clause in a PHP script. This can be achieved by using the mysqli_query() or mysql_query() function to select records from a MySQL table with the LIKE clause, which can be combined with the WHERE clause in SQL.


Question:

I encountered an error while attempting to utilize LIKE in an IF statement in mySQL.

SELECT nc.web_name AS company_name,
    IF((b.booth_number LIKE '%Cafe%', b.booth_number, substring(b.booth_number,4)) AS booth_number, 
    nc.site_url AS website, IF (nc.ismi_status = 1, 'Member','') AS member_status 
    FROM booth_sale bs JOIN {ismi.booth} b ON bs.booth_id = b.booth_id 
    JOIN ismi.new_people np ON bs.contact_id = np.id 
    JOIN ismi.new_company nc ON nc.id = np.company_id 
    WHERE b.show_event_id = 298 AND status IN(44,45) ORDER BY 3

The query works fine if I do this:

IF((b.booth_number = 'Cafe', b.booth_number, substring(b.booth_number,4)) AS booth_number

To ensure accuracy, I must verify
multiple possibilities
which may have variations like Cafe, Cafe1, Cafe2, etc. To clarify, I intend to retrieve

b.booth_number

for any entry that contains Cafe in that field.

This is the error I get:

The SQL query with ID 1064 has an error in its syntax. Please refer to the manual for the correct syntax to utilize. The error occurs near the code snippet: “AS booth_number, nc.site_url AS website, IF (nc.ismi_status = 1, ‘Member’,”)”. The query selects the company name as “nc.web_name”, booth number as “IF((b.booth_number LIKE ‘%Cafe%’, b.booth_number, substring(b.booth_number,4)) AS booth_number”, website as “nc.site_url”, and member status as “IF (nc.ismi_status = 1, ‘Member’,”)”. It uses several joins to retrieve information from the tables {ismi.booth_sale}, {ismi.booth}, {ismi.new_people}, and {ismi.new_company}. The query only selects data where the show event ID equals 298 and the status is either 44 or 45. Finally, the query sorts the data by the third column.

Is there a way to rectify my mistake or an alternative approach to accomplish this task?


Solution:

Your

IF

code contains excessive opening parentheses. Removing the first one should resolve the issue, assuming that there are no additional SQL errors.

...
IF(b.booth_number LIKE '%Cafe%', b.booth_number, substring(b.booth_number,4)) AS booth_number, 
...

Frequently Asked Questions