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

Answer

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.