Search code examples
sqlgoogle-bigquery

Big Query - Get continuous 7 characters from a long string in Big Query

I need to look for 7 char long alphanumeric string that should always be in format of NNCCNNN, where N denotes Number, C Denotes Character and X denotes either a number or character.

For Example - For Car ID AB-CC-16TG842-ABA3864, ID should be 16TG842

For Car ID CD-CCIAL_50056956_15AD864, ID should be 15AD864

For Car ID FG-ENNNNN.OOPPLLI_PLOKI-17, ID should be Blank.

I tried using BYTE_LENGTH() and regex_extract() but couldn't get it as expected.

Could anyone help with this tricky one?

Solution

  • Here is a direct solution to your puzzle:

    with data as (
  SELECT "AB-CC-16TG842-ABA3864" as car UNION ALL
  SELECT "CD-CCIAL_50056956_15AD864" UNION ALL
  SELECT "FG-ENNNNN.OOPPLLI_PLOKI-17"
)
SELECT REGEXP_EXTRACT(car, r"[[:digit:]][[:digit:]][[:alpha:]][[:alpha:]][[:digit:]][[:digit:]][[:digit:]]") as id FROM data

    It leverages REGEX_EXTRACT