Question 112712 in Mysql Question posted in
The official MySQL documentation can be found here.

mysql search street and street number

I have a customer table like this

id | city          | street            | street number
1  | Berlin        | Bahnhofstraße     | 5
2  | New York      | Main street       | 22
3  | Frankfurt     | Bahnhofstraße     | 11
4  | London        | Bond Street       | 63

I made a simple html input, get the value with jquery and perform an simple search with ajax.

So when i search for "Bahnh" i fire the SQL query

SELECT * FROM `customers` WHERE `street ` LIKE '%Bahnh%' OR city LIKE '%"Bahnh"%'

I get the id 1 and 3

But is there a way to add the street number in the query? Like when i search for "Bahnhof 11" i only get id 3?

Tags:

3

Answers


  1. 0

    Just add another criterion to your WHERE clause:

    SELECT *
FROM customers
WHERE street LIKE 'Bahnh%' AND street_number = 11;
    Login or Signup to reply.
  2. 0

    Add a condition

    SELECT * 
FROM `customers` 
WHERE `street number` = 11 
AND (`street ` LIKE '%Bahnh%' OR city LIKE '%"Bahnh"%')

    SELECT *
FROM (SELECT ID, CITY, STREET, STREET NUMBER, CONCAT('STREET', CONCAT(' ', STREET NUMBER)) TEST FROM `CUSTOMERS`)
WHERE TEST LIKE '%Bahnh 11'
    Login or Signup to reply.
  3. 0

    From what I understand you have a single search field for address where the user can type both street name and street number. You can create a full text search index on both columns:

    ALTER TABLE customers 
ADD FULLTEXT INDEX fulltext(street, street_number);

    Read more about FULLTEXT SEARCH: https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html

    Then you have to manipulate the string you search by adding + and * at on every word. The final SQL should look like this:

    SELECT * 
FROM streets 
WHERE MATCH (street, number) AGAINST ('+Bahnh* +11*' IN BOOLEAN MODE);

    + means that every word is mandatory, * is wild card like % in LIKE queries.

    One caveat: you cannot search inside the string with this SQL, only strings that start with Bahnh* – But it will solve your problem on searches like ’11 Bahnh’ or ‘Bahnh 11’ etc…

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search