CODEWITHSUNDEEP
stringsplitgoogle-bigquery
Ilja
Ilja

Reputation: 631

BigQuery: How to see a part of the string?

I have a database that looks like

 Alex,Anna,Peter
 Alex
 Alex,Peter
 Alfons
 Algebra,Geometry
 Algebra,Physics

However I am only interested in the first expression before the comma. Meaning my perfect answer would be:

Alex
Alex
Alex
Alfons
Algebra
Algebra

So far I found the SPLIT function but it still returns me a bunch of values I am really not interested in. How to make it run efficiently?

Thanks

Upvotes: 1

Views: 524

Answers (2)

Mosha Pasumansky
Mosha Pasumansky

Reputation: 14014

SPLIT could be expensive, since it translates string into repeated field, so here are two more alternative solutions:

SELECT IFNULL(LEFT(s, INSTR(s, ',') - 1), s)
FROM
  (SELECT 'Alex,Anna,Peter' AS s),
  (SELECT 'Algebra,Geometry' AS s),
  (SELECT 'Alfons' AS s)

and

SELECT REGEXP_EXTRACT(s, r'([^,]*)')
FROM
  (SELECT 'Alex,Anna,Peter' AS s),
  (SELECT 'Algebra,Geometry' AS s),
  (SELECT 'Alfons' AS s)

Upvotes: 0

Pentium10
Pentium10

Reputation: 208042

SELECT first(split(s,','))
FROM
  (SELECT 'Alex,Anna,Peter' AS s),
  (SELECT 'Algebra,Geometry' AS s);

Outputs:

Alex     
Algebra

Upvotes: 2

Related Questions