0

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 orangi,faisal,nazimabad.

Towns that are stored in the table are

  • Orangi
  • Korangi
  • Shah Faisal
  • Shahrah-e-Faisal
  • Nazimabad
  • 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

Expected Outcome

  • 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

  • Use FIND_IN_SET() – Akina Feb 8 at 21:29
  • Thanks for the answer @Akina, but That doesn't work with wildcards and I guess with index also ? – Hamza Khanzada Feb 8 at 21:30
  • Program a loop which runs over all searchtowns and construct a select statement,PREPARE stmt which adds for every city a Where town LIKE '%searchcity%' it os a bit tricky because you must link them with OR but doable. – nbk Feb 8 at 21:39
  • @nbk can you show me a snippet to get started from ? – Hamza Khanzada Feb 9 at 9:33
  • You say orangi,faisal,nazimabad is the input? What should the output be? – Rick James Feb 13 at 22:36
2
  • Use REPLACE(SearchTowns, ',', '|') to change commas to pipes.
  • Put that result in, say, @regexp.
  • Construct, via CONCAT a single SELECT with

    SET @sql = CONCAT('SELECT
      name,
      Town,
      FROM userLocation where Town REGEXP "', @regexp, '"');
    
  • PREPARE and EXECUTE the query.

That query should look like:

SELECT
      name,
      Town,
      FROM userLocation where Town REGEXP "orangi|faisal|nazimabad"

(You could manually run that to verify that the correct rows are delivered.)

Also, be sure to have case-folding COLLATION, such as utf8mb4_unicode_520_ci for Town. (That is indicated by the trailing _ci.)

Code

DELIMITER //
CREATE PROCEDURE `GetUsersByTown`(
    IN `SearchTowns` VARCHAR(200) -- `ORANGI,FAISAL,NAZIMABAD`
)
BEGIN
IF (SearchTowns IS NULL) THEN
    SELECT name, Town
        FROM userLocation;
ELSE
    SET @towns = REPLACE(SearchTowns, ',', '|');
    SELECT name, Town
        FROM userLocation
        WHERE Town REGEXP (@towns);
END IF;
END //
DELIMITER ;
  • this solution may require check for sql injection – Nikita Feb 14 at 9:21
  • SET \@sql = 'SELECT name, Town, FROM userLocation where Town REGEXP ?'; EXECUTE @sql USING @regexp; – Nikita Feb 14 at 9:35
  • @RickJames, can you please show the complete procedure ? – Hamza Khanzada Feb 14 at 10:49
  • Why do I have to Prepare and Execute the query? Like why we have to create the query in string format first and then use prepare and execute to that query? why can't we directly use the query? – Hamza Khanzada Feb 14 at 11:08
  • How can I make it to return all records if the parameter is null? – Hamza Khanzada Feb 14 at 20:06
0

CREATE PROCEDURE GetUsersByTown( IN SearchTowns LONGTEXT ) BEGIN SELECT name, Town, FROM userLocation where FIND_IN_SET(Town,(SearchTowns))>0 END

New contributor
Koteswara Rao is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
  • Hello @Koteswara, Welcome to StackExchange, As I have mentioned in the question, Find_In_SET does not accept wildcards hence that will return only Exact matches – Hamza Khanzada Feb 12 at 10:39
  • you can try this – Koteswara Rao Feb 12 at 12:08
  • CREATE PROCEDURE GetUsersByTown( IN SearchTowns LONGTEXT ) BEGIN SELECT name, Town, FROM userLocation t inner join (select Town from userLocation where find_in_set(Town,(SearchTowns))>0) n ON t.Town like concat(n.Town,'%') END – Koteswara Rao Feb 12 at 12:08
  • Can you please update your answer with the updated code? so that I can have a better look and what you are trying to say – Hamza Khanzada Feb 12 at 14:19
  • @KoteswaraRao this procedure will not return users from Korangi if searching string doesn't contain this town but contains Orangi, or if it contains only part of town name from the middle – Nikita Feb 12 at 20:24
0

This is a stored procedure which splits inputed comma-separated string (SearchTowns) into separate rows, inserts these rows into temporary table (Towns) and then uses join between userLocation and Towns tables on required conditions (where userLocation.town is like %town% from Towns table). All other rows which don't correspond this condition will be eliminated from the final resultset.
Also I used DISTINCT to avoid rows duplication in case if userLocation.town corresponds to more than one row from Towns table. For example, if SearchTowns contains 'Orangi' and 'Korangi' then users who are located in Korangi will be joined with both towns (because 'korangi' like '%orangi%').

CREATE PROCEDURE GetUsersByTown(SearchTowns VARCHAR(200))

    BEGIN
      DECLARE pos INT Default 0 ;
      DECLARE str VARCHAR(200);
      CREATE TEMPORARY TABLE Towns(town VARCHAR(200));
      #Loop through comma-separated values
      #pos - is the number of current word within search string
      simple_loop: LOOP
         SET pos=pos+1;
         SET str=REPLACE(SUBSTRING(SUBSTRING_INDEX(SearchTowns, ',', pos),
                    LENGTH(SUBSTRING_INDEX(SearchTowns, ',', pos -1)) + 1),
                    ',', '');
         IF str='' THEN
            LEAVE simple_loop;
         END IF;
         #Do Inserts into temp table here with str going into the row
         insert into Towns values (str);
    END LOOP simple_loop;
    SELECT DISTINCT
      u.name,
      u.Town
    FROM userLocation AS u
    JOIN Towns AS t ON u.Town LIKE CONCAT('%', t.town, '%');
   DROP TEMPORARY TABLE Towns;
END
  • Please explain your answer @Nikita – Hamza Khanzada Feb 12 at 11:41
  • @HamzaKhanzada added an explanation – Nikita Feb 12 at 15:51
  • Thankyou for the code Nikita, I will accept the answer shortly after testing all the results – Hamza Khanzada Feb 13 at 5:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.