Panduan SQL SUBSTRING

SUBSTRING mengembalikan sebagian string, dimulai dari posisi yang Anda tentukan dan sepanjang jumlah karakter yang Anda minta. Dengan cara inilah SQL mengambil prefiks, kode, atau domain email dari kolom teks. Contoh-contohnya berjalan di PostgreSQL, MySQL, SQLite, dan SQL Server.

Cara menggunakan SUBSTRING

Berikan SUBSTRING sebuah string, posisi awal (karakter pertama adalah 1), dan jumlah karakter yang diambil. Hilangkan panjangnya untuk mengambil semuanya sampai akhir:

SUBSTRING(string, start, length)

SELECT SUBSTRING('Joe Bloggs', 1, 3);
-- Output: Joe

-- No length: the rest of the string (SQL Server needs all three arguments)
SELECT SUBSTRING('Joe Bloggs', 5);
-- Output: Bloggs

Dalam query sungguhan, string-nya biasanya berupa kolom. Nomor pesanan seperti ORD-2024-001 menyimpan tahun di karakter 5 sampai 8:

-- Input: orders
-- order_number
-- ------------
-- ORD-2024-001
-- ORD-2025-014
-- ORD-2026-102

SELECT order_number, SUBSTRING(order_number, 5, 4) AS year
FROM orders;

-- Output:
-- order_number | year
-- -------------+-----
-- ORD-2024-001 | 2024
-- ORD-2025-014 | 2025
-- ORD-2026-102 | 2026

Aturan yang perlu diketahui

• Posisi dimulai dari 1, bukan 0. Posisi awal negatif dihitung mundur dari akhir string, tetapi hanya di MySQL dan SQLite:

SELECT SUBSTRING('Joe Bloggs', -6, 6);
-- Output: Bloggs
-- PostgreSQL and SQL Server return '' (an empty string) instead

• Melewati akhir string tidak menimbulkan error. Anda mendapatkan karakter yang ada, atau string kosong jika posisi awal melewati akhir:

SELECT SUBSTRING('Bob', 1, 10);
-- Output: Bob

SELECT SUBSTRING('Hello', 10, 5);
-- Output: '' (an empty string)

• NULL masuk, NULL keluar. Bungkus pemanggilannya dengan COALESCE jika Anda butuh sebuah nilai:

SELECT SUBSTRING(NULL, 1, 5);
-- Output: NULL
-- SQL Server rejects a bare NULL here, but a NULL column gives NULL

• Filter berdasarkan prefiks dengan LIKE, bukan SUBSTRING. WHERE SUBSTRING(product_code, 1, 3) = 'ELE' mengembalikan baris yang sama dengan WHERE product_code LIKE 'ELE%', tetapi hanya versi LIKE yang bisa memakai index pada product_code.

Sintaks alternatif

Anda juga akan menemui SUBSTR, nama lain untuk SUBSTRING, serta LEFT dan RIGHT, yang mengambil karakter dari salah satu ujung:

-- Not in SQL Server
SELECT SUBSTR('Joe Bloggs', 1, 3);
-- Output: Joe

-- Not in SQLite: use SUBSTRING('Joe Bloggs', 1, 3) and SUBSTRING('Joe Bloggs', -6)
SELECT LEFT('Joe Bloggs', 3);
-- Output: Joe

SELECT RIGHT('Joe Bloggs', 6);
-- Output: Bloggs

-- PostgreSQL and MySQL: the standard SQL form
SELECT SUBSTRING('Joe Bloggs' FROM 1 FOR 3);
-- Output: Joe

Untuk memulai setelah karakter tertentu, cari dulu posisinya:

SELECT INSTR('joe@example.com', '@');
-- Output: 4
-- PostgreSQL: POSITION('@' IN 'joe@example.com'); SQL Server: CHARINDEX('@', 'joe@example.com')

Kapan sebaiknya memakai SUBSTRING?

Gunakan SUBSTRING ketika bagian string yang Anda butuhkan berada di posisi yang diketahui, atau tepat setelah karakter yang bisa Anda temukan. Sebagian besar penggunaannya termasuk salah satu dari empat kasus ini.

Ambil beberapa karakter pertama

Kode dan nama sering kali bermakna di bagian awalnya: kategori dalam kode produk, inisial, atau cuplikan singkat dari deskripsi panjang. SUBSTRING(column, 1, n) mengambil n karakter pertama.

Ambil field dari nilai berformat tetap

Jika setiap nilai memiliki tata letak yang sama, setiap bagian berada di posisi yang sama di setiap baris: kode area dalam nomor telepon seperti (303) 555-0147, tahun dalam ORD-2024-001, atau field dari record lebar tetap dari sistem lama.

Ekstrak teks setelah pemisah

Domain email, ekstensi file, dan path URL dimulai setelah karakter yang posisinya berubah dari baris ke baris. Cari posisinya dengan INSTR, mulai satu karakter setelahnya, lalu ambil sisa string-nya.

Simpan beberapa karakter terakhir

Menyamarkan nomor kartu atau rekening, atau membaca sufiks, berarti menghitung mundur dari akhir. Hitung posisi awal dari panjang string, atau, di MySQL dan SQLite, gunakan posisi awal negatif.

Contoh nyata SUBSTRING

Hitung produk berdasarkan kategori dalam kodenya

Kode produk diawali kategori tiga huruf. Kelompokkan berdasarkan prefiks itu untuk menghitung produk di setiap kategori:

-- Input: products
-- product_code | name
-- -------------+-----------
-- ELE-1234     | USB-C Hub
-- FUR-0087     | Desk Chair
-- ELE-5521     | Webcam
-- OFF-2210     | Stapler

SELECT SUBSTRING(product_code, 1, 3) AS category, COUNT(*) AS products
FROM products
GROUP BY SUBSTRING(product_code, 1, 3)
ORDER BY category;

-- Output:
-- category | products
-- ---------+---------
-- ELE      | 2
-- FUR      | 1
-- OFF      | 1

Parsing record lebar tetap

Ekspor dari sistem lama sering menempatkan field di posisi tetap tanpa pemisah. Di sini nama mengisi 8 karakter (diisi spasi, yang dihapus oleh TRIM), tanggal lahir 8 karakter berikutnya, dan kode kota 3 karakter terakhir:

-- Input: legacy_data
-- record
-- -------------------
-- JOHNDOE 19850315NYC
-- MARYLEE 19780912CHI
-- JANEROE 19900722LAX

SELECT
  TRIM(SUBSTRING(record, 1, 8)) AS name,
  SUBSTRING(record, 9, 8) AS birth_date,
  SUBSTRING(record, 17, 3) AS city_code
FROM legacy_data;

-- Output:
-- name    | birth_date | city_code
-- --------+------------+----------
-- JOHNDOE | 19850315   | NYC
-- MARYLEE | 19780912   | CHI
-- JANEROE | 19900722   | LAX

Ambil ekstensi setiap file

Cari titiknya, lalu ambil semua setelahnya. Klausa WHERE melewati file yang tidak memilikinya:

-- Input: documents
-- filename
-- ---------------
-- q3-report.pdf
-- budget.xlsx
-- README
-- team-photo.jpeg

SELECT
  filename,
  SUBSTRING(filename, INSTR(filename, '.') + 1) AS extension
FROM documents
WHERE INSTR(filename, '.') > 0;

-- Output:
-- filename        | extension
-- ----------------+----------
-- q3-report.pdf   | pdf
-- budget.xlsx     | xlsx
-- team-photo.jpeg | jpeg

Di PostgreSQL, gunakan POSITION('.' IN filename) sebagai pengganti INSTR. Di SQL Server, gunakan CHARINDEX('.', filename) dan tambahkan panjang, misalnya LEN(filename).

Samarkan nomor kartu

Tidak semua nomor kartu terdiri dari 16 digit, jadi posisi awal yang tetap akan memotong nomor American Express. Sebaiknya hitung mundur dari akhir:

-- Input: payments
-- customer_name | card_number
-- --------------+-----------------
-- Lena Fischer  | 4111111111111111
-- Marcus Reid   | 5500005555555559
-- Aiko Tanaka   | 378282246310005

-- Wrong: position 13 only works for 16-digit cards
SELECT customer_name, '**** ' || SUBSTRING(card_number, 13, 4) AS masked_card
FROM payments;

-- Output:
-- customer_name | masked_card
-- --------------+------------
-- Lena Fischer  | **** 1111
-- Marcus Reid   | **** 5559
-- Aiko Tanaka   | **** 005

-- Right: start 4 characters from the end
SELECT customer_name, '**** ' || SUBSTRING(card_number, LENGTH(card_number) - 3, 4) AS masked_card
FROM payments;

-- Output:
-- customer_name | masked_card
-- --------------+------------
-- Lena Fischer  | **** 1111
-- Marcus Reid   | **** 5559
-- Aiko Tanaka   | **** 0005

Di MySQL dan SQL Server, gabungkan string dengan CONCAT() sebagai pengganti ||. SQL Server juga memerlukan LEN() sebagai pengganti LENGTH().

Bacaan lanjutan tentang SUBSTRING

Di blog ini:

• Panduan SQL LIKE: cocokkan teks yang diawali, diakhiri, atau mengandung suatu pola
• Panduan SQL COALESCE: ganti NULL yang diteruskan oleh SUBSTRING
• Panduan SQL GROUP BY: kelompokkan dan hitung baris berdasarkan nilai yang Anda ekstrak
• Panduan format tanggal SQL: format tanggal dan ambil bagian tanggal tanpa memotong string

Dokumentasi resmi:

• PostgreSQL: Fungsi string
• MySQL: SUBSTRING()
• SQLite: substr()
• SQL Server: SUBSTRING