Searching a formatted address in SQL table where columns are split

Ask Time：2020-07-25T13:51:16 Author：upsidedownb

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

Author:upsidedownb，eproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article：https://stackoverflow.com/questions/63084875/searching-a-formatted-address-in-sql-table-where-columns-are-split

Gordon Linoff :

I would strongly recommend concat_ws() in this case over concat():\nwhere concat_ws(' ', number, street, type, unit) like '%12 Smith Street%'\n\nNot 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.\nHowever, 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.",

2020-07-25T12:24:02

Searching a formatted address in SQL table where columns are split