LIKE %NAME% in a Store Procedure

User, date Message
Written by adamsea888@gmail.com
3 years ago
Category: General
2 posts since Thu, 04 Aug 11

I have a stored proc wihch takes in one param. It has the following code

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_GetStreetName`(
IN STREET_NAME varchar(50)
SELECT `name` as MY_NAME from table_address
where street LIKE %STREET_NAME%;

The above query has a syntax error - complaining about the % signs.

I also used:
where street LIKE STREET_NAME;

and in the parameter, I specified the param value as "%"+street+"%" - but the query returned no results.

My question is, how do I do a [street LIKE '%Brown%'] but from within a stored procedure?

Written by ansgar
3 years ago
5015 posts since Fri, 07 Apr 06
I guess it should look like this:

... where street LIKE CONCAT('%', STREET_NAME, '%');

Written by kalvaro
3 years ago
594 posts since Thu, 29 Nov 07
... because, in SQL, strings go single-quoted.
Written by adamsea888@gmail.com
3 years ago
2 posts since Thu, 04 Aug 11
It worked! Thanks so much!

Please login to leave a reply, or register at first.