Big Query - String Function

Question

I am very new in BigQuery platform, i want to take the following strings SOCKETIOEXCEPTION##APS.COM, NULLPOINTEREXCEPTION##RSJAVA.COM, CLASSCASTEEXCEPTION##MPS.COM

And get this as a result: SOCKETIOEXCEPTION, NULLPOINTEREXCEPTION, CLASSCASTEEXCEPTION

Before ## characters I want to separate from a given string and then I want to group by number rows available in the above-mentioned tag like SOCKETIOEXCEPTION, NULLPOINTEREXCEPTION, CLASSCASTEEXCEPTION

Sample db details

How do I write this query?

Clarify wha the output you expect! And have tried anything? Even if it didn't work - show us what it was. Would be great if you can provide simplified example of input data and expected output — Mikhail Berlyant
– Mikhail Berlyant, Commented Feb 6, 2019 at 5:56

Mikhail Berlyant · Accepted Answer · 2019-02-06 06:24:15Z

Below is for BigQuery Standard SQL

#standardSQL
SELECT SPLIT(line, '##')[OFFSET(0)] type, COUNT(1) cnt 
FROM `project.dataset.table`
GROUP BY type

You can test, play with above using sample data from your question as in example below

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 'SOCKETIOEXCEPTION##111' line UNION ALL
  SELECT 'SOCKETIOEXCEPTION##222' UNION ALL
  SELECT 'SOCKETIOEXCEPTION##333' UNION ALL
  SELECT 'NULLPOINTEREXCEPTION##444' UNION ALL
  SELECT 'NULLPOINTEREXCEPTION##555' UNION ALL
  SELECT 'CLASSCASTEEXCEPTION##666' UNION ALL
  SELECT 'CLASSCASTEEXCEPTION##777' UNION ALL
  SELECT 'CLASSCASTEEXCEPTION##888' 
)
SELECT SPLIT(line, '##')[OFFSET(0)] type, COUNT(1) cnt 
FROM `project.dataset.table`
GROUP BY type

with result

Row type                    cnt  
1   SOCKETIOEXCEPTION       3    
2   NULLPOINTEREXCEPTION    2    
3   CLASSCASTEEXCEPTION     3

Collectives™ on Stack Overflow

Big Query - String Function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related