parse each row of the table and display distinct values sql

Question

I am working on a public dataset where I need to parse each row of the Table Posts. So, for example, three rows under the column tags have values:

1. <c#><.net>
2. <android> <python> <sockets> <android-studio><socket.io>
3. <android-studio><socket.io><.net>

I need to display the output as:

1. c#
2. .net
3. android
4. python
5. sockets
6. android-studio
7. socket.io

Is there a way I could do it in BigQuery?

1) mysql or bigquery? 2) what you tried so far? I downvoted because [no attempt was made](http://idownvotedbecau.se/noattempt/) — Mikhail Berlyant, Jul 19 '19 at 20:54
i have no idea how to do this I'm a beginner and tried this https://dba.stackexchange.com/questions/127540/return-top-100-words-from-strings — madi, Jul 19 '19 at 21:03
@Mahad This might get you closer to what you need https://stackoverflow.com/questions/19413901/splitting-a-string-column-in-bigquery — Error_2646, Jul 19 '19 at 21:11
@Error_2646 I will try it and let you know if it works. Thank you very much. — madi, Jul 19 '19 at 21:15

score 1 · Answer 1 · answered Jul 19 '19 at 21:24

1

You can first use REGEXP_EXTRACT_ALL to match all the words inside < >'s, and then UNNEST the resulting arrays to flatted the output:

select e from (
select regexp_extract_all(x, r'<([^>]*)>') e
from unnest([
"<c#><.net>",
"<android> <python> <sockets> <android-studio><socket.io>",
"<android-studio><socket.io><.net>"
]) x), unnest(e) e

Produces

Row e   
1       c#
2       .net
3       android
4       python
5       sockets
6       android-studio
7       socket.io
8       android-studio
9       socket.io
10      .net

And to eliminate duplicates - run DISTINCT:

select distinct e from (
select regexp_extract_all(x, r'<([^>]*)>') e
from unnest([
"<c#><.net>",
"<android> <python> <sockets> <android-studio><socket.io>",
"<android-studio><socket.io><.net>"
]) x), unnest(e) e

answered Jul 19 '19 at 21:24

Mosha Pasumansky

13,206
5
32
55

Thank you very much. for your support – madi Jul 19 '19 at 22:32
i did it like this – madi Jul 19 '19 at 22:32
SELECT REGEXP_EXTRACT(Tags, r'<([^>]*)>') As T FROM [sotorrent-org:2018_09_23.Posts] GROUP BY T – madi Jul 19 '19 at 22:32
Looks like you are using Legacy SQL, I recommend switching to Standard SQL (my solution uses it) – Mosha Pasumansky Jul 19 '19 at 23:07
It looks like the answer solved the question. If that's true, it is a good idea to accept the answer, but if not please indicate what went wrong. – rsantiago Jul 22 '19 at 22:43

score 0 · Answer 2 · answered Jul 22 '19 at 23:11

Below is for BigQuery Standard SQL

#standardSQL
SELECT DISTINCT tag
FROM `project.dataset.table`,
UNNEST(REGEXP_EXTRACT_ALL(tags, r'<([^>]*)>')) tag

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

#standardSQL
WITH `project.dataset.table` AS (
  SELECT '<c#><.net>' tags UNION ALL
  SELECT '<android> <python> <sockets> <android-studio><socket.io>' UNION ALL
  SELECT '<android-studio><socket.io><.net>'
)
SELECT DISTINCT tag
FROM `project.dataset.table`,
UNNEST(REGEXP_EXTRACT_ALL(tags, r'<([^>]*)>')) tag

with result

Row tag  
1   c#   
2   .net     
3   android  
4   python   
5   sockets  
6   android-studio   
7   socket.io

parse each row of the table and display distinct values sql

2 Answers2