CODEWITHSUNDEEP
google-bigquerydata-analysis
Adishwor Pradhan
Adishwor Pradhan

Reputation: 29

BigQuery extract string between two "."

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.

Upvotes: 0

Views: 170

Answers (1)

Jaytiger
Jaytiger

Reputation: 12254

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.

Upvotes: 1

Related Questions