CODEWITHSUNDEEP
mysqlsql
upsidedownb
upsidedownb

Reputation: 39

Searching a formatted address in SQL table where columns are split

How would I go about searching (and possibly full-text index searching) on a table for address data that is structured as per below:

address table
+----+------+--------+--------+--------+-------------+-------+----------+
| id | unit | number | street |  type  |    town     | state | postcode |
+----+------+--------+--------+--------+-------------+-------+----------+
|  0 |    2 |     12 | Smith  | Street | Springfield | NSW   |     1234 |
|  1 |    0 |     16 | Bob    | Road   | Faketown    | VIC   |     4321 |
+----+------+--------+--------+--------+-------------+-------+----------+

When searching for an address I'd like to search for the whole address. Ideally I'd like to be able to search the table like this:

SELECT * FROM addresses WHERE address LIKE "%12 Smith Street%"

Thanks for your help in advance

Upvotes: 0

Views: 203

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

I would strongly recommend concat_ws() in this case over concat():

where concat_ws(' ', number, street, type, unit) like '%12 Smith Street%'

Not only is it shorter to type and less prone to error, but it ignores NULL values. If any value for concat() is NULL the entire result is NULL.

However, for multi-column full-text search, you should consider using match(). Full text search gives you more flexibility on the searching, although you need to be careful about stop words and short words.

Upvotes: 1

Abra
Abra

Reputation: 20913

You can use the CONCAT function to create a string from the columns.

select CONCAT(number, ' ', street, ' ', type) address
  from address_table
 where address like '%12 Smith Street%'

You didn't say what the data types are of the columns in address_table. You may need to convert numerical columns to strings.

Upvotes: 1

Related Questions