Substring function in BigQuery with examples

BigQuery Substring

Substring function is used to extract the portion of the string from a given string. Since Substring is an alias of substr in BigQuery, either Substring or substr can be used to get the substring.

Substring syntax in BigQuery

  • value – The input value can be a String or Byte value.
  • position – It is an integer value which denotes the starting position of the substring.  
    • position = 1 is indicating the first character or byte.
    • If the position is negative, the function counts from the end of value with -1 indicating the last character.
    • If the position = 0 or less than -LENGTH(value), the substring starts from position 1.
  • length – It is an optional argument. It specifies the number of characters to be returned from the starting position.
    • If the length is not specified, it returned the characters from staring position to last character of the string.

Example 1 : Substring with input string in BigQuery

In this example, we have used SUBSTR function to extract the string Science from the input string DataScience.

Substring position in BigQuery
Substring position in BigQuery

If we calculate the position of the string DataScience from 1, the string “Science” is starting from position 5. So we have mentioned the position as 5 in the Substring function.

Since the length is not mentioned in substring, it returns all the characters from the position 5.

Substring function example in BigQuery
Substring function example in BigQuery

Example 2 : Substring with column in BigQuery

Consider that we have a table credit_card_info which holds the credit card details of customers as below. Here rc_fin_test_tables is a dataset name.

Sample data for Substring in BigQuery
Sample data for Substring in BigQuery

As we highlighted in the image, the credit card number is present in the column card_number. The data type of this column is String data type. In order to identify the issuer identification number, We want to extract the digits from position 2 to 6. Let’s write a substring function for this.

Substring with column name in BigQuery
Substring with column name in BigQuery

In the substring function, we have mentioned the starting position as 2 and length of the string is 6. So it returned the 6 characters from the position 2 of card_number.

Example 3 : Get last few characters using substring

Let’s say we want to get the last 3 digits from the credit card number. In that case, we can use the negative position number in the substring function.

As we mentioned earlier, the negative position -1 denotes the last character of string. In order to get the last 3 character, we have given the position as -3 and ignored the length argument. So it will return the characters from positions -3,-2 & -1.

Last few character using substring function in BigQuery
Last few character using substring function in BigQuery

Finally the substring function in BigQuery returned the last 3 characters from credit card number based on negative position number. Along with substring function, we can use other string functions also. Example: INSTR, TRIM, LENGTH, UPPER, LOWER,..etc.

Recommended Articles