Get multiple occurrence of string from a column in sql query

user_1023554 0
Viewed 440

I have a table which has the following data 

Ticketid    created     Details
205853669   2020-03-05  #CLOSE# Next action  value://346004/ next action  value://346002/ or value://346008/
205853670   2020-03-06  #Archive Next action  value://346088/ next action  value://346077/ or value://346057/

The string "value://" pattern is same in all column, I want to extract those numbers from the string.

ticketid    Numbers
205853669   346004
205853669   346002
205853669   346008
205853670   346088
205853670   346077
205853670   346057

I am using standard Sql only I have created something like below.

select ticketid,TRIM(REPLACE(SUBSTR(
    details, STRPOS(details, "value//"),10
  ),"value//"","")) AS number from table
google-bigquery sql
2 Answers

Below is for BigQuery Standard SQL 

#standardSQL
SELECT Ticketid, Numbers
FROM `project.dataset.table`,
UNNEST(REGEXP_EXTRACT_ALL(Details, r'value://(\d+)/')) Numbers

If to apply to sample data from your question - output is 

Row Ticketid    Numbers  
1   205853669   346004   
2   205853669   346002   
3   205853669   346008   
4   205853670   346088   
5   205853670   346077   
6   205853670   346057
user_52219440

The below query would work. This query splits the comment on value then extracts the 6 digit id.

with `project.dataset.table` as (
select id, split(details, 'value://') AS number from (
   select '1' as id, '#CLOSE# Next action  value://346004/ next action  value://346002/ or value://346008/' as details
   union all
   select '2' as id, '#Archive Next action  value://346088/ next action  value://346077/ or value://346057/'
  )
)
select id, regexp_extract(number1, "\\d{6}") as number
from `project.dataset.table` ,
UNNEST( number ) number1
where regexp_extract(number1, "\\d{6}") is not null

It has one remark about UNNEST function. As per documentation

The UNNEST operator takes an ARRAY and returns a table, with one row for each element in the ARRAY.

If you have only a few 'values://' for each comment then this wouldn't cause as much problem, but if there would be unlimited number of 'value://' this might become a performance bottleneck so keep that in mind. On the other hand this is the only way I know how to achieve that using CloudSQL.

user_103271890
Related