CODEWITHSUNDEEP
google-bigquery
Daniel Zrůst
Daniel Zrůst

Reputation: 211

Transforming Values Within Array in BigQuery

assume I have a table with column looking like this: de/a/la/les/un/une/oi/ke/el/para/d/pour/for/l/en/ (this is all 1 value in 1 column on 1 row).

I want to convert these values to %de /%a /%la /%les /.... and so on.

Basically, I am trying to build a basis for regexp expression which I will use later.

So I figured, I need to convert my original string to ARRAY by using SPLIT(orignal,'/') and then convert every item from the array by CONCAT('%',original,' ')... But I am failing to write a query which would work...

Any ideas?

DZ

Upvotes: 1

Views: 525

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173046

Below is for BigQuery Standard SQL 

REGEXP_REPLACE(original, r'(\w+)', r'%\1 ') AS somecolumnname

You can test, play with it using sample data from your question as below 

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 'de/a/la/les/un/une/oi/ke/el/para/d/pour/for/l/en/' original
)
SELECT
  REGEXP_REPLACE(original, r'(\w+)', r'%\1 ') AS somecolumnname
FROM `project.dataset.table`

with result as 

Row somecolumnname   
1   %de /%a /%la /%les /%un /%une /%oi /%ke /%el /%para /%d /%pour /%for /%l /%en /

Upvotes: 1

Daniel Zrůst
Daniel Zrůst

Reputation: 211

Ok, so here is the solution:

SELECT

ARRAY_TO_STRING(ARRAY(SELECT CONCAT(' ',original_single,' ') FROM UNNEST(SPLIT(original,'|')) AS original_single),'|') AS somecolumnname

FROM.....

Upvotes: 1

Related Questions