CODEWITHSUNDEEP
sqlgoogle-bigquery
Maykid
Maykid

Reputation: 517

How to remove only letters from a string in BigQuery?

So I'm working with BigQuery SQL right now trying to figure out how to remove letters but keep numeric numbers. For example:

XXXX123456
AAAA123456789
XYZR12345678
ABCD1234567
1111
2222

All have the same amount of letters in front of the numbers along with regular numbers no letters. I want the end result to look like:

123456
123456789
12345678
1234567
1111
2222

I tried using PATINDEX but BigQuery doesn't support the function. I've also tried using LEFT but that function will get rid of any value and I don't want to get rid of any numeric value only letter values. Any help would be much appreciated!

-Maykid

Upvotes: 8

Views: 25181

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269543

You can use regexp_replace():

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

Upvotes: 15

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172974

Below example is for BigQuery Standard SQL 

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 'XXXX123456' str UNION ALL
  SELECT 'AAAA123456789' UNION ALL
  SELECT 'XYZR12345678' UNION ALL
  SELECT 'ABCD1234567' UNION ALL
  SELECT '1111' UNION ALL
  SELECT '2222' 
)
SELECT str, REGEXP_REPLACE(str, r'[a-zA-Z]', '') str_adjusted
FROM `project.dataset.table`

Upvotes: 5

Related Questions