Substring function in BigQuery with examples
Contents
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
|
1 2 3 |
SUBSTRING(value, position[, length]) (or) SUBSTR(value, position[, length]) |
- 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.
|
1 2 |
SELECT SUBSTR('DataScience', 5) as example; |
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.
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.
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.
|
1 2 3 |
SELECT card_number, SUBSTR(card_number,2,6) as issuer_identification_number FROM rc_fin_test_tables.credit_card_info; |
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.
|
1 2 3 |
SELECT card_number, SUBSTR(card_number,-3) as account_number FROM rc_fin_test_tables.credit_card_info; |
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.
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
- Regexp_replace function in BigQuery with examples
- Regexp_extract function in BigQuery with examples
- String REPLACE function in BigQuery with examples
