Скачать или смотреть Finding an Alternative to REGEXP_INSTR in BigQuery

Discover how to find the position of the first occurrence of an alphabet in a string using alternatives to REGEXP_INSTR in BigQuery. This blog explains the method step-by-step with examples.
---
This video is based on the question https://stackoverflow.com/q/62652439/ asked by the user 'Thirumalai Muthu Palani' ( https://stackoverflow.com/u/11317161/ ) and on the answer https://stackoverflow.com/a/62663189/ provided by the user 'Mikhail Berlyant' ( https://stackoverflow.com/u/5221944/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Alternative to REGEXP_INSTR in Bigquery

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Finding an Alternative to REGEXP_INSTR in BigQuery: A Comprehensive Guide

BigQuery offers robust tools for data analysis, but certain functions, like REGEXP_INSTR, may not be available. This can lead to challenges when trying to extract specific information from strings, such as determining the position of the first letter in a numerical-alphabetic string. In this guide, we will explore how to address this challenge with a practical alternative solution tailored for BigQuery’s SQL.

The Challenge

Let’s understand the problem through a practical example. Suppose you have a column called invoice_number with values like:

123A45

54B381

1A2B21

You need to find the position of the first occurrence of an alphabet character in each of these strings. Traditionally, you might use the REGEXP_INSTR function to do this. However, since this function is not available in BigQuery, we will explore an alternative approach.

The Solution: Using REGEXP_EXTRACT

While the REGEXP_INSTR function is not provided, BigQuery allows us to use REGEXP_EXTRACT to achieve a similar result. Here’s the step-by-step breakdown of how to implement this:

SQL Syntax

The following SQL code will allow you to find the position of the first alphabetic character in the invoice_number column:

[[See Video to Reveal this Text or Code Snippet]]

Explanation of the SQL Code

REGEXP_EXTRACT(invoice_number, r'^(\d*)[^\d]'):

This part of the query extracts the numeric portion from the start of the invoice number until the first non-numeric character (the first letter).

In this case, \d* matches any digit, and [^\d] identifies the first non-digit character.

LENGTH(...) + 1:

The LENGTH function calculates the length of the extracted numeric string.

By adding 1, you compute the position of the first letter in the original string.

Sample Output

If we apply our SQL query to the provided sample data, we would get the following results:

Rowinvoice_numberfirst_occurrence_of_the_alphabet1123A454254B381331A2B212Conclusion

By leveraging BigQuery's REGEXP_EXTRACT function, we can successfully find the position of the first occurrence of an alphabet character in a string without relying on REGEXP_INSTR. This method serves as an effective alternative that fulfills the requirement for string manipulation in BigQuery.

If you have further questions or need clarification on using these functions, feel free to reach out! Happy querying!