max consecutive digits in a string

Lucas Dresl 0
Viewed 80

I am trying to count the number of MAX consecutive digits that appear in a string column, let me give an example to illustrate better what I am trying to do. If I have a table called email

email
lucas1234@gmail.com
fer12@gmail.com
lupal@gmail.com
carlos1perez222@gmail.com
carlos11perez222@gmail.com
lucila1@gmail.com

my expected output would be

                    email  count_cons_digits
      lucas1234@gmail.com                  4
          fer12@gmail.com                  2
          lupal@gmail.com                  0
carlos1perez222@gmail.com                  3

carlos11perez222@gmail.com 3 lucila1@gmail.com 1

Check that this question is very similar to : Number of consecutive digits in a column string

but the only difference is that the function from the results is not contemplating cases with only one digit in the email (like lucila1@gmail.com). In this case, the expected result should be 1 but the proposed function is giving 0. And also whenever the email contains "two sections" of consecutive digits (carlos11perez222@gmail.com). In this case, the expected output is to be 3 but is given 5.

google-bigquery
2 Answers

Consider below approach

select *, 
  ifnull((select length(digits) len
    from unnest(regexp_extract_all(email, r'\d+')) digits
    order by len desc 
    limit 1
  ), 0) as count_cons_digits
from your_table

if applied to sample data in your question - output is

enter image description here

Mikhail Berlyant0

You may also try this approach using regex:

WITH email AS
(SELECT 'lucas1234@gmail.com' mail,
UNION ALL SELECT 'fer12@gmail.com',
UNION ALL SELECT 'lupal@gmail.com',
UNION ALL SELECT 'carlos1perez222@gmail.com',
UNION ALL SELECT 'carlos11perez222@gmail.com',
UNION ALL SELECT 'lucila1@gmail.com')

SELECT email,
(LENGTH(REGEXP_REPLACE(REGEXP_REPLACE(email.mail, r'[A-Za-z]+\d+[A-Za-z]+', ''),r'[A-Za-z.@]+',''))) AS count_cons_digits,
FROM email;

Output: enter image description here

Anjela B0
Related