String Manipulation

ARRAY_TO_STRING([CAST(child_orders.id AS string), CAST(parent_orders.id AS string), CAST(parent_parent_orders.id AS string), CAST(parent_parent_parent_orders.id AS string)], ' < ' ) AS order_id_trail

Make a customer name concatenation with:

TRIM(ARRAY_TO_STRING([CAST(customers.name AS string), CAST(customers.first_name AS string)], ', ' )) AS customer_name

Make price range string

ARRAY_TO_STRING([

TRIM(COALESCE(SAFE_CAST(categories.new_purchase_price AS string format '999,999,990.00'),'none')),

TRIM(COALESCE(SAFE_CAST(categories.very_good_purchase_price AS string format '999,999,990.00'),'none)),

TRIM(COALESCE(SAFE_CAST(categories.good_purchase_price AS string format '999,999,990.00'),'none))

],'/')

Flatten an Array to Rows

You can split listed elemnts into arrays, and flatten those arrays into rows with an unnest statement:

SELECT

my_element_array.item,

flattened_element

FROM(

WITH

with_items_element_lists AS(

SELECT

'A' AS item,

'a||b||c' AS elements

UNION ALL

SELECT

'X' AS item,

'x||y||z' AS elements)

SELECT

with_items_element_lists.item,

SPLIT(with_items_element_lists.elements,'||') AS split_element

FROM

with_items_element_lists) AS my_element_array

CROSS JOIN

UNNEST(my_element_array.split_element) AS flattened_element ;

The from statement with the unnest can with shorthanded:

...

FROM

with_items_element_lists) AS my_element_array

, (my_element_array.split_element) AS flattened_element ;

Split String

SELECT

word

FROM (

SELECT

t.name,

SPLIT(NORMALIZE_AND_CASEFOLD(t.name),' ') AS name_split

FROM

`my_table_with_names` AS t

WHERE

LOWER(t.link) LIKE 'my_string_start_%'

AND NULLIF(TRIM(t.name),'') IS NOT NULL ) AS names,

names.name_split AS word

Duplicate by Quantity

You can duplicate an output row by repeating, splitting, and unnesting it.

SELECT

split_array.inbound_registration_id,

ROW_NUMBER() OVER (PARTITION BY split_array.inbound_registration_id) AS instance_parcel

FROM (

WITH

with_ircs AS (

SELECT

i.inbound_registration_id,

i.parcel_count,

REPEAT('|', i.parcel_count-1) AS counter

FROM

`rere-bi-dev.business_vault.inbounds_view` AS i

WHERE

i.inbound_registration_id IS NOT NULL

AND i.inbound_registration_id IN (2033,

2079,

2091))

SELECT

with_ircs.*,

SPLIT(with_ircs.counter,'|') AS splitter

FROM

with_ircs) AS split_array

CROSS JOIN

UNNEST(split_array.splitter) AS flats;

Substring

RegEx Substring Pattern

You can get e.g. the the first purchaser id of the article no with:

LEFT(REGEXP_EXTRACT(line_items.article_nos,r'[A-Z][0-9]-'),2) AS line_item_article_no_created_by_purchaser_id

Fetch Substring at End

fetches substring after the last / in string

SPLIT(t.name,'/')[ARRAY_LENGTH(SPLIT(t.name,'/'))-1] AS name_end

Padding

SELECT

FORMAT('%04d',SAFE_CAST(39.95 AS INT64)) AS makes_0040,

FORMAT('%04d',SAFE_CAST(CEIL(39.95) AS INT64)) AS makes_0040,

FORMAT('%04d',SAFE_CAST(FLOOR(39.95) AS INT64)) AS makes_0039,

LPAD(SAFE_CAST(TRUNC(39.95) AS STRING),4,"0") AS makes_0039;

Formatting

FORMAT()

Format Number to String

format elements

Create '1,312.40' from 1312.4.

SAFE_CAST(1312.4 AS STRING FORMAT '999,990.00') AS string_number_with_dividers

Proper

proper is called INITCAP()

Empty String

replace empty strings with NULLIF(), e.g.:

NULLIF(TRIM(customers.name),'') AS emtpy_to_null