CODEWITHSUNDEEP
mysqlstringgoogle-bigquerysubstringtrim
Chique_Code
Chique_Code

Reputation: 1530

trim the first few characters of string in BigQuery MySQL

I need to grab the first few letters from the strings in BigQuery table. My data looks like this:

name
HDM | kjfsgjfgusgff
GHN | hgfhgfhgsf
GHT | 57fhjsfhjsgfghg

The desired output is: HDM, GHN, GHT I have tried the following:

SELECT TRIM(SUBSTRING_INDEX(name, '|', 1) as `new_name`

Got an error: Function not found: substring_index

Upvotes: 2

Views: 9975

Answers (3)

Cylldby
Cylldby

Reputation: 1978

Your have several options depending on the shape of your data. The TRIM + SPLIT option suggested by Mikhail, which is very close to what you tried

SELECT TRIM(SPLIT(name, "|")[offset(0)])
FROM table

The LEFT option maybe the easiest (only if there is a fixed character length that you're interested in:

SELECT LEFT(name,3)
FROM table

The regex option too if you need more precise control:

SELECT REGEXP_EXTRACT(name, r"^([A-Z]+)(?:|)")
FROM table

EDIT:

And Sergey's SUBSTR solution:

SELECT SUBSTR(name, 1, 3)
FROM table

Upvotes: 5

Sergey Geron
Sergey Geron

Reputation: 10172

Try substr:

select substr(name, 1, 3) as new_name from mytable

Upvotes: 1

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

For BigQuery - use below

select trim(split(name, '|')[offset(0)])
from your_table

if applied to sample data in your question - output is

enter image description here

Upvotes: 2

Related Questions