CODEWITHSUNDEEP
sqlgoogle-bigquery
user3422637
user3422637

Reputation: 4229

Google Big Query SQL to extract numeric ID from string

How do I write a SQL Query in Google Big Query to extract numeric ID from a string like these:

Example 1: Column Value: "http://www.google.com/abc/eeq/entity/32132"

Desired Extraction: 32132

Example 2: Column Value: "http://www.google.com/abc/eeq/entity/32132/ABC/2138"

Desired Extraction: 32132

Example 3: Column Value: "http://www.google.com/abc/eeq/entity/32132http://www.google.com/abc/eeq/entity/32132"

Desired Extraction: 32132

Upvotes: 0

Views: 1483

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

Below example for BigQuery Standard SQL 

#standardSQL
WITH `project.dataset.table` AS (
  SELECT "http://www.google.com/abc/eeq/entity/32132" url UNION ALL
  SELECT "http://www.google.com/abc/eeq/entity/32132/ABC/2138" UNION ALL
  SELECT "http://www.google.com/abc/eeq/entity/32132http://www.google.com/abc/eeq/entity/32132" 
)
SELECT url, REGEXP_EXTRACT(url, r'\d+') extracted_id 
FROM `project.dataset.table`

with output 

Row url                                                                             extracted_id     
1   http://www.google.com/abc/eeq/entity/32132                                          32132    
2   http://www.google.com/abc/eeq/entity/32132/ABC/2138                                 32132    
3   http://www.google.com/abc/eeq/entity/32132http://www.google.com/abc/eeq/entity/32132    32132

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You can use regexp_extract(). To get the first series of digits in the string:

select regexp_extract(col, '[0-9]+')

Upvotes: 1

Related Questions