BigQuery extract string between two "."

Adishwor Pradhan 0
Viewed 46

I have three string types: 'en-ae.sssports.com','amazon.co.uk','farfetch.com'

I require the client name i.e. Sssports, Amazon, Farfetch from each.

Had tried using regexp '(?:.)[^.]*(?:.)' to extract string between the two dots but I require a dynamic code that extracts data from all three sting types.

data-analysis google-bigquery
1 Answers

You may try below query

SELECT INITCAP(RTRIM(NET.REG_DOMAIN(str), '.' || NET.PUBLIC_SUFFIX(str))) AS client_name
  FROM UNNEST(['en-ae.sssports.com','amazon.co.uk','farfetch.com']) str

Query reesults

+-------------+
| client_name |
+-------------+
| Sssports    |
| Amazon      |
| Farfetch    |
+-------------+

References

Takes a URL as a STRING and returns the registered or registerable domain (the public suffix plus one preceding label), as a STRING.

Takes a URL as a STRING and returns the public suffix (such as com, org, or net) as a STRING.

Jaytiger0
Related