String
BigQuery
SUBSTR
BigQueryExtracts a portion of characters from a string starting at a specified position. An alias for SUBSTRING, highly useful for data extraction and parsing.
Tipe hasil:
STRINGDiperbarui: 13 Jun 2026Syntax
SQL
SUBSTR(value, position [, length])Parameter
valueSTRINGwajib
The string to extract from
positionINT64wajib
The starting position (1-indexed). Negative values count from the end of the string
lengthINT64opsional
The number of characters to extract. If omitted, extracts through the end of the string
Contoh Penggunaan
Extract Characters from the Start
SQL
1 SELECT SUBSTR('Hello World', 1, 5) as result;
Extracts the first 5 characters.
Hasil
Hello
Extract from a Specific Position
SQL
1 SELECT SUBSTR('Hello World', 7) as result;
Extracts from position 7 through the end of the string.
Hasil
World
Negative Position (from End)
SQL
1 SELECT SUBSTR('Hello World', -5) as result;
Extracts the last 5 characters.
Hasil
World
Extract Code Components from an ID
SQL
1 SELECT 2 product_id, 3 SUBSTR(product_id, 1, 3) as category_code, 4 SUBSTR(product_id, 4, 4) as product_code, 5 SUBSTR(product_id, -2) as variant_code 6 FROM `project.dataset.products` 7 LIMIT 3;
Parses components from a product_id in the format: CAT0001XX
Hasil
| product_id | category_code | product_code | variant_code |
|---|---|---|---|
| ELC0001AB | ELC | 0001 | AB |
| FSN0025XL | FSN | 0025 | XL |
Pertanyaan Umum tentang SUBSTR
Apa itu fungsi SUBSTR di BigQuery?
Extracts a portion of characters from a string starting at a specified position. An alias for SUBSTRING, highly useful for data extraction and parsing. Di BigQuery, fungsi SUBSTR termasuk dalam kelompok fungsi yang sering digunakan untuk mengolah dan menganalisis data secara efisien.
Bagaimana cara menggunakan SUBSTR di BigQuery?
Gunakan sintaks berikut: SUBSTR(value, position [, length]). Pastikan argumen yang dimasukkan sudah sesuai dengan tipe data yang diharapkan.
Apa nilai yang dikembalikan oleh fungsi SUBSTR?
Fungsi SUBSTR mengembalikan nilai bertipe STRING. Pastikan tipe data hasil sudah sesuai dengan kebutuhan query atau formula kamu. Jika input mengandung nilai NULL, perilaku fungsi dapat berbeda - selalu periksa dokumentasi untuk memastikan hasilnya sesuai ekspektasi.
Fungsi Equivalen di Database Lain
Ngulik Langsung
Latihan Langsung →Sudah paham SUBSTR? Latih langsung di browser
Latihan interaktif, langsung di browser.