Number of consecutive digits in a column string

June 21, 2022

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

email
lucas1234@gmail.com
fer12@gmail.com
lupal@gmail.com
carlos1perez222@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

>Solution :

You could use a regex replacement with length trick:

SELECT email,
       LENGTH(email) - LENGTH(REGEXP_REPLACE(email, '[0-9]{2,}', '')) AS count_cons_digits
FROM yourTable;

Note that this answer assumes that there would be at most one segment of a given email string having continuous digits. If not, and there could be more than one, then you would need to define what happens in that case.