The SPLIT() function takes an input string value and divides it into individual elements. The first and second arguments are strings and optional delimiters.
The shipping address column is an example of how this function works in the data. Here’s how you can use the function to split the components: street address, city, post code and country. The following code was prepared for this.
SELECT customer,
shipping_address,
SPLIT(shipping_address) AS address_split
FROM orders.orders;| customer | shipping_address | address_split |
| Jones & Son, Inc. | 10-2 Parkson St, Boston, MA 02136, USA | |
| Jones & Son, Inc. | 10-2 Parkson St, Boston, MA 02136, USA | |
| Stavros GmbH | Landsberger Alee 49, Berlin, 10249, Germany | |
| Hendricks, Hendricks, Hoekstra | Anna Paulownastraat 23, Rotterdam, 3014 JA, Netherlands | |
| Mckenna & Partners | 2-8 Ingram St, Glasgow, G1 1HA, UK |
As you observe, the shipping address is broken up into the field you entered. The products are terrific, and I would recommend that you consider them. However, this is only the preview of the shipping history, which is the first time I have seen a program do this.
It would be best if there were fields for the different fields of the address, so you could have them individually displayed. The only way for BigQuery to work in this scenario is to override the calculation. As a possible example, we are calculating the same string to create an income statement and a comparison using an accounting software.
SELECT customer,
shipping_address,
SPLIT(shipping_address, ', ') [SAFE_ORDINAL (1)] AS street,
SPLIT(shipping_address, ', ') [SAFE_ORDINAL (2)] AS city,
SPLIT(shipping_address, ', ') [SAFE_ORDINAL (3)] AS postal_code,
SPLIT(shipping_address, ', ') [SAFE_ORDINAL (4)] AS country
FROM orders.orders; SPLIT() assists with breaking down an address into its components for each separate element of the data. This is because I do not want any of the separate words to serve as a column header. Integer values are determined using the SAFE_ORDINAL() function. This code will match against any text that contains street address, city, state, or ZIP code, returning their first values, respectively.
| customer | shipping_address | street | city | postal_code | country |
| Jones & Son, Inc. | 10-2 Parkson St, Boston, MA 02136, USA | 10-2 Parkson St | Boston | MA 02136 | USA |
| Jones & Son, Inc. | 10-2 Parkson St, Boston, MA 02136, USA | 10-2 Parkson St | Boston | MA 02136 | USA |
| Stavros GmbH | Landsberger Alee 49, Berlin, 10249, Germany | Landsberger Alee 49 | Berlin | 10249 | Germany |
| Hendricks, Hendricks, Hoekstra | Anna Paulownastraat 23, Rotterdam, 3014 JA, Netherlands | Anna Paulownastraat 23 | Rotterdam | 3014 JA | Netherlands |
| Mckenna & Partners | 2-8 Ingram St, Glasgow, G1 1HA, UK | 2-8 Ingram St | Glasgow | G1 1HA | UK |