I am creating a stored procedure to search user's Towns.
From the User Interface user will select multiple towns that will be sent to the stored procedure.
Sample input of the towns parameter for the stored procedure would be like
Towns that are stored in the table are
- Shah Faisal
- North Nazimabad
Now I want to select all those above records against that input. That is I want to use
LIKE operator with
IN from a stored procedure's parameter.
I don't know how many towns would be there in the input. it could be just one, or two or three or all these mentioned above.
I want to search all the towns against that query
Sample Stored Procedure is
CREATE PROCEDURE `GetUsersByTown`( IN `SearchTowns` VARCHAR(200) -- `ORANGI,FAISAL,NAZIMABAD` ) BEGIN SELECT name, Town, FROM userLocation where Town in (SearchTowns) END
- Name --- Town
- Hamza --- Orangi
- Jamal --- Korangi
- Hasham --- Shah Faisal
- Aliyan --- Shahrah-e-Faisal
- Danish --- Nazimabad
- Farrukh --- North Nazimabad
I have tried to use
find_in_set() but FIND_IN_SET does not accept wildcards like
% and it doesn't use index?
I have also looked at using
REGEXP ValueA|ValueB but I don't know how to put the input parameter that is comma separated values in that REGEXP