Extract string of numbers from variable with Query

Question

I have a table in BigQuery with a variable with strings of text like this:

row  path
1    /998777/kkjs/lksjaflkjsdf/sdfñklñ{sdf
2   /ñljasdfkgbsdf/5854654/lsjflkjsdf/asdfasdfsdf
3    /11544/sdfsdf/asdfsdfasdfdsf/sssfdsfdsdf

How can I query this table to extract the numbers for the variable so I get a new variable with:

row  path2
1    998777
2    5854654
3    11544

Thx!

Mikhail Berlyant · Accepted Answer · 2019-05-21 01:28:09Z

1

Below example for BigQuery Standard SQL

#standardSQL
WITH `project.dataset.table` AS (
  SELECT '/998777/kkjs/lksjaflkjsdf/sdfñklñ{sdf' path UNION ALL
  SELECT '/ñljasdfkgbsdf/5854654/lsjflkjsdf/asdfasdfsdf' UNION ALL
  SELECT '/11544/sdfsdf/asdfsdfasdfdsf/sssfdsfdsdf' 
)
SELECT path, REGEXP_EXTRACT(path, r'/(\d+)/') path2
FROM `project.dataset.table`

with result

Row path                                            path2    
1   /998777/kkjs/lksjaflkjsdf/sdfñklñ{sdf           998777   
2   /ñljasdfkgbsdf/5854654/lsjflkjsdf/asdfasdfsdf   5854654  
3   /11544/sdfsdf/asdfsdfasdfdsf/sssfdsfdsdf        11544

edited May 21, 2019 at 1:28

answered May 21, 2019 at 1:08

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

EGM8686 Over a year ago

Edited the question as I only want to retain full set of numbers among // al the rest should be excluded

Gordon Linoff · Accepted Answer · 2019-05-21 01:08:02Z

0

You can use regexp_replace():

select regexp_replace(str, '[^0-9]', '')

answered May 21, 2019 at 1:08

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

2 Comments

EGM8686 Over a year ago

This keeps all numbers however there are numbers among the rest of the string such as: /998777/kkjs/lksjaflkjsdf/sdfñklñ9ddsdf/99h/ so this includes 99 as well which is not the expected behaviour

Gordon Linoff Over a year ago

@EGM8686 . . . There are none in your sample strings and it is not clear if you want to keep all digits or just specific digits. If specific digits, you should be clear on what the rules are.

Collectives™ on Stack Overflow

Extract string of numbers from variable with Query

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related