Sql query for searching every value after every space in string

Question

I am writing query to obtain results from two columns. what i wants is

when i send this string "family apotheke 14.05" from my search input then the query should run like this

  Select * 
    From mytable 
   Where street LIKE '%family apotheke%' 
  AND/OR mycustomdate LIKE '%14.05'

So that i can get the result whatever is true. If family apotheke found in columns then show all if date found date column then show its result and if both results found then show all results found.

Hope this will be clear

see the required result

This make no sense at all. Your sample string has two spaces. — Gordon Linoff, May 16 '17 at 10:45
Please show us several sample records and the output you expect. — Tim Biegeleisen, May 16 '17 at 10:45
@ Gordon Linoff yes two spaces one with alphabets and one with int like value so we can differentiate that i think by splitting the string — Hassaan, May 16 '17 at 10:48
@Tim i have provided the image after edit so see the required result — Hassaan, May 16 '17 at 10:51

score 0 · Accepted Answer · edited May 23 '17 at 12:26

0

First you have to filter number from the string and second you need string without number, you can achieve this:

$str = 'family apotheke 14.05';
preg_match_all('!\d+\.*\d*!', $str ,$match);
echo $number = implode(' ', $match[0]);
echo "<br />".$filter_str =  trim(str_replace($number, '', $str));

Now you can write your query like :

 Select * 
    From mytable 
   Where street LIKE "%$filter_str%" 
  AND/OR mycustomdate LIKE "%$number"

It is up to you, whether you want AND or OR in your query.

Demo

Reference - Extract numbers from a string

edited May 23 '17 at 12:26

Community

1
1

answered May 16 '17 at 11:07

Bhaskar Jain

1,651
1
12
20

It is giving me 0 result when i am searching with AND. – Hassaan May 16 '17 at 11:39
do you have any record in `mycustomdate` which contains 14.05? – Bhaskar Jain May 16 '17 at 11:41
If you are using `AND`, both string/condition must match to get results, in the provided screen shot in OP, there is no matching records. – Bhaskar Jain May 16 '17 at 11:50
It can't match because the "Apotheke" is translated into English as "pharmacy" and the date is written as "05/14". – Adder May 16 '17 at 12:00
@GuanapadoPado you need to modify your query : which ever you want use that - `Select * From mytable Where street LIKE "%$filter_str%" AND mycustomdate LIKE "%$number%"` or `Select * From mytable Where street LIKE "%$filter_str%" AND mycustomdate LIKE "$number%"` – Bhaskar Jain May 16 '17 at 12:03
ok now working as there was a problem with putting the value inside the quotation , we were putting this "%$filter_str%" instead of this "%'.$filter_str%.'" – Hassaan May 16 '17 at 12:37
Glad to help you. :) – Bhaskar Jain May 16 '17 at 13:03
hey can you please tell me if i put 14.05.2017 even 14.05. then the query found no result but when i write 14.05 it works fine.How can i get full date result – Hassaan May 16 '17 at 13:36
where are you putting 14.05.2017? in yr string or in query? – Bhaskar Jain May 16 '17 at 13:38
i am writing in my search bar like this ( Family Apotheke 15.04.2017 ) – Hassaan May 16 '17 at 14:00
so it gives me this result SELECT * From ticket_master Where (street LIKE "%Family Apotheke 15.04.2017%") AND (ticket_date LIKE "%15.04 2017%") order by ticket_id desc also i write Family 15.04.2017 it gives me this result SELECT * From ticket_master Where (street LIKE "%Family 15.04.2017%") AND (ticket_date LIKE "%15.04 2017%") order by ticket_id desc – Hassaan May 16 '17 at 14:02
you need to update these two line: `preg_match_all('!\d+\.*\d*.*\d*!', $str ,$match); echo $number = implode('', $match[0]);` – Bhaskar Jain May 16 '17 at 14:04

Sql query for searching every value after every space in string

1 Answers1