CODEWITHSUNDEEP
google-bigquery
Oksana Ok
Oksana Ok

Reputation: 555

Remove File extension from the name by using BigQuery

What is the way to remove file extensions from the name by using BigQuery?

E.g. --- XXX.jpg to XXX and e.t.c

I found a lot of solutions in SQL Server, but nothing for BigQUery

REVERSE(SUBSTRING(REVERSE([field]),0,CHARINDEX('[expr]',REVERSE([field]))))

Upvotes: 0

Views: 942

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173076

Below example is just one of the many options for BigQuery Standard SQL 

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 'xxx.jpg' name_with_extension UNION ALL
  SELECT 'yyy.png' UNION ALL
  SELECT 'aaa.bbb.xyz'
)
SELECT name_with_extension, 
  REGEXP_EXTRACT(name_with_extension, r'(.*)\..*$') name
FROM `project.dataset.table`

with result 

Row name_with_extension name     
1   xxx.jpg             xxx  
2   yyy.png             yyy  
3   aaa.bbb.xyz         aaa.bbb

Or using string manipulation functions: 

#standardSQL
SELECT name_with_extension, 
  SUBSTR(name_with_extension, 1, LENGTH(name_with_extension) - STRPOS(REVERSE(name_with_extension), '.')) name
FROM `project.dataset.table`

obviously with the same output

Upvotes: 1

Related Questions