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

6
  • Use FIND_IN_SET()
    – Akina
    Feb 8, 2020 at 21:29
  • Thanks for the answer @Akina, but That doesn't work with wildcards and I guess with index also ? Feb 8, 2020 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, 2020 at 21:39
  • @nbk can you show me a snippet to get started from ? Feb 9, 2020 at 9:33
  • You say orangi,faisal,nazimabad is the input? What should the output be?
    – Rick James
    Feb 13, 2020 at 22:36

3 Answers 3

3
+50
  • 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 ;
8
  • this solution may require check for sql injection Feb 14, 2020 at 9:21
  • SET \@sql = 'SELECT name, Town, FROM userLocation where Town REGEXP ?'; EXECUTE @sql USING @regexp; Feb 14, 2020 at 9:35
  • @RickJames, can you please show the complete procedure ? Feb 14, 2020 at 10:49
  • 1
    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? Feb 14, 2020 at 11:08
  • How can I make it to return all records if the parameter is null? Feb 14, 2020 at 20:06
0

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

5
  • 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 Feb 12, 2020 at 10:39
  • you can try this Feb 12, 2020 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 Feb 12, 2020 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 Feb 12, 2020 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 Feb 12, 2020 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
3
  • Please explain your answer @Nikita Feb 12, 2020 at 11:41
  • @HamzaKhanzada added an explanation Feb 12, 2020 at 15:51
  • Thankyou for the code Nikita, I will accept the answer shortly after testing all the results Feb 13, 2020 at 5:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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