How to Interpolate variables in mysql pattern matching query?

Question

This is my code to search database entries with pattern as a value in parameter PLACE1,

DROP PROCEDURE `casetwosplit`;

CREATE DEFINER=`root`@`localhost` PROCEDURE `case` (IN `PLACE1` LONGTEXT)
       NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER 

select all PLACE,CITY,LATTITUDE,LONGITUDE 
from LOCATION 

where PLACE like @'%PLACE1%

On execution, I am getiing no result eventhough the values are present. When the value in the variable PLACE1 is provided explicitly, I am getting the result. But when the pattern in the like clause has the variable name it's not interpolating the variable value in the query.

Your question could be improved by formatting the code (sql in your case) as such as well as supplying some example data, that help understanding what is going on. — hotzst
– hotzst, Commented Jan 14, 2016 at 11:46

PaulF · Accepted Answer · 2016-01-14 11:43:03Z

4

MySQL will not automatically replace parameter names within the string so you are always looking for a place name containing the string "PLACE1". What you need to do is add the wildcard characters to the parameter :

select all PLACE,CITY,LATTITUDE,LONGITUDE from LOCATION     
where PLACE like CONCAT('%', PLACE1, '%')

answered Jan 14, 2016 at 11:43

PaulF

6,8032 gold badges21 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Darshan K S Over a year ago

Thank You, I got what i needed.

PaulF Over a year ago

If you mean that I answered your question then you can mark it as a correct answer - that will help anybody else searching with a similar problem.

Collectives™ on Stack Overflow

How to Interpolate variables in mysql pattern matching query?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related