CODEWITHSUNDEEP
sqlgoogle-bigquery
Pierre-Alexandre
Pierre-Alexandre

Reputation: 765

Trim after a split of a string on BigQuery

I am trying to trim my SYMB variable which is a simple string such as John or Lucy. There is usually some extra space and I would like to remove those extra spaces using TRIM inside a WITH AS clause

WITH students AS (
    SELECT 
    AZE,
    EPI,
    SYMB,
    from schools.boston, 
    unnest(split(AZE, ',') || split(EPI, ',')) as SYMB)

example input data

{
  "id": 1,
  "AZE": "english , chinese , french",
  "EPI": "math, physics"
},
{
  "id": 2,
  "AZE": "",
  "EPI": "computer science"
},
{
  "id": 3,
  "AZE": "taiwanese, russian, italian, turkish",
  "EPI": "painting, drawing"
}

output SYMB rows

id 1 - row 1:english 
id 1 - row 2: chinese --> extra space to remove
id 1 - row 3: french  --> extra space to remove
id 1 - row 4:math 
id 1 - row 5: physics --> extra space to remove

The only location where I was able to use trim in my query was in the SELECT but it is only doing the TRIM before the split process. What would be the best way to apply a TRIM to my SYMB variable?

 WITH students AS (
        SELECT 
        trim(AZE) as AZE,
        trim(EPI) as EPI,
        SYMB,
        from schools.boston, 
        unnest(split(AZE, ',') || split(EPI, ',')) as SYMB)

Upvotes: 0

Views: 1617

Answers (2)

rtenha
rtenha

Reputation: 3616

with data as (
    select 1 as id, 'english, chinese, french' as AZE, "math, physics" as EPI   union all 
    select 2, "", "computer science"    union all 
    select 3, "taiwanese, russian, italian, turkish", "painting, drawing"
),
split_and_concat as (
    select 
        data.*, split(AZE, ',') || split(EPI, ',') as SYMB
    from data
),
trimmed as (
    select
        split_and_concat.*, trim(item) as item
    from split_and_concat,
    unnest(SYMB) as item
)
select id, item
from trimmed

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269553

SYMB is the result of the unnest(), so that is what you want to trim:

select . . . , trim(symb)

Upvotes: 2

Related Questions