#Mysql like how to

#Do Inserts into temp table here with str going into the row LENGTH(SUBSTRING_INDEX(SearchTowns, ',', pos -1)) + 1), SET str=REPLACE(SUBSTRING(SUBSTRING_INDEX(SearchTowns, ',', pos), #pos - is the number of current word within search string CREATE PROCEDURE GetUsersByTown(SearchTowns VARCHAR(200))ĬREATE TEMPORARY TABLE Towns(town VARCHAR(200)) 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%'). All other rows which don't correspond this condition will be eliminated from the final resultset.Īlso I used DISTINCT to avoid rows duplication in case if userLocation.town corresponds to more than one row from Towns table. 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).

#Mysql like how to

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 I have tried to use find_in_set() but FIND_IN_SET does not accept wildcards like % and it doesn't use index? IN `SearchTowns` VARCHAR(200) - `ORANGI,FAISAL,NAZIMABAD`įROM userLocation where Town in (SearchTowns) Sample Stored Procedure is CREATE PROCEDURE `GetUsersByTown`( I want to search all the towns against that query It could be just one, or two or three or all these mentioned above. I don't know how many towns would be there in the input.

That is I want to use LIKE operator with IN from a stored procedure's parameter. Now I want to select all those above records against that input. Sample input of the towns parameter for the stored procedure would be like orangi,faisal,nazimabad. The following is the query to use single Like with OR operator − Case 1 − Using Like with OR operator mysql> select *from LikeDemo where Id Like 'John123%' or Id Like 'Carol9091%' or Id Like 'David2345%' ģ rows in set (0.I am creating a stored procedure to search user's Towns.įrom the User Interface user will select multiple towns that will be sent to the stored procedure. The query is as follows − mysql> select *from LikeDemo Mysql> insert into LikeDemo values('David2345') ĭisplay all records from the table with the help of select statement. Mysql> insert into LikeDemo values('Johnson2222') Mysql> insert into LikeDemo values('Carol9091') Then you can get the values that match the pattern mentioned by using the LIKE query in SQL. When you only know a fragment of a text value and need to get the details from the table.

Mysql> insert into LikeDemo values('Bob999') LIKE query is used to search for a particular pattern from the table rows and return the columns, which matches the pattern. Mysql> insert into LikeDemo values('Smith205') The query is as follows − mysql> insert into LikeDemo values('John123') Now you can insert records in the table with the help of insert statement. The query to create a table is as follows − mysql> create table LikeDemo To understand both the syntaxes, let us create a table. select *from yourTableName where IN(value1,value2,value3.N) select *from yourTableName where yourColumnName Like ‘Value1’ Let us see the syntax for both the cases − Case 1 − Using Like with OR operator. You can use LIKE with OR operator which works same as IN operator.