Fungsi String

Nama pelanggan, alamat email, kode produk — sebagian besar kolom di database dunia nyata bertipe teks, dan teks yang diinput manusia hampir selalu datang dalam keadaan kotor: ada spasi berlebih di ujung, kapital yang tidak konsisten, atau format yang harus dirapikan sebelum ditampilkan. PostgreSQL menyediakan keluarga fungsi string yang menyelesaikan pekerjaan itu langsung di dalam query, tanpa perlu memindahkan data ke aplikasi. Chapter ini memetakan fungsi-fungsi tersebut per kelompok tugas: mengukur, membersihkan, memotong, menggabungkan, mengganti, dan memformat. Setiap kelompok ditunjukkan dengan contoh pendek yang bisa langsung dijalankan pada tabel produk kecil milik Toko Maju Jaya berikut ini.

CREATE TABLE produk (
    id    integer PRIMARY KEY,
    kode  varchar(10) NOT NULL,
    nama  text NOT NULL
);

INSERT INTO produk (id, kode, nama) VALUES
    (1, 'P101', 'Kopi Gayo'),
    (2, 'P102', '  teh melati  '),
    (3, 'P103', 'GULA MERAH'),
    (4, 'P104', 'susu kedelai 1 liter');

SELECT id, kode, nama FROM produk ORDER BY id;

Output yang Dihasilkan:

CREATE TABLE
INSERT 0 4
 id | kode |         nama         
----+------+----------------------
  1 | P101 | Kopi Gayo
  2 | P102 |   teh melati  
  3 | P103 | GULA MERAH
  4 | P104 | susu kedelai 1 liter
(4 rows)

Perhatikan baris kedua: nama ' teh melati ' disimpan lengkap dengan spasi di kedua ujungnya — PostgreSQL tidak pernah membersihkan input secara diam-diam, sehingga tanggung jawab merapikan teks ada di tangan kita. Baris lainnya sengaja dibuat dengan gaya kapital yang berbeda-beda supaya fungsi upper, lower, dan initcap punya bahan yang nyata untuk dibedah.

Mengukur Panjang dan Mengatur Kapital #

Dua tugas paling dasar pada teks adalah menghitung panjangnya dan menyeragamkan huruf besar-kecilnya. PostgreSQL menyediakan beberapa fungsi yang tampak mirip tetapi punya perbedaan halus:

Untuk teks ASCII biasa, ketiganya menghasilkan angka yang sama; octet_length baru berbeda ketika teks memuat karakter multi-byte seperti emoji atau huruf beraksen. Fungsi kapitalisasi di sisi lain bekerja per kata:

Mari kita terapkan pada tabel produk:

-- length() dan char_length() menghitung jumlah karakter,
-- spasi dihitung sebagai karakter biasa
SELECT nama,
       length(nama) AS panjang,
       char_length(nama) AS panjang_karakter
FROM produk
ORDER BY id;

-- tiga fungsi kapitalisasi
SELECT initcap('toko maju jaya') AS judul,
       upper('kopi gayo') AS huruf_besar,
       lower('GULA MERAH') AS huruf_kecil;

Output yang Dihasilkan:

         nama         | panjang | panjang_karakter 
----------------------+---------+------------------
 Kopi Gayo            |       9 |                9
   teh melati         |      14 |               14
 GULA MERAH           |      10 |               10
 susu kedelai 1 liter |      20 |               20
(4 rows)

     judul      | huruf_besar | huruf_kecil 
----------------+-------------+-------------
 Toko Maju Jaya | KOPI GAYO   | gula merah
(1 row)

Hasil pertama memperlihatkan jebakan yang sering mengejutkan pemula: length(' teh melati ') mengembalikan 14, bukan 10, karena spasi di ujung dihitung sebagai karakter biasa. Panjang kolom yang tampak “wajar” di layar ternyata bisa menyimpan kejutan spasi tersembunyi — itulah alasan fungsi trim di bagian berikutnya sangat sering dipakai. Fungsi initcap memperlakukan batas kata sebagai batas kapitalisasi, sehingga 'susu kedelai 1 liter' akan menjadi 'Susu Kedelai 1 Liter' — angka tidak diganggu, huruf pertama setelah spasi yang dinaikkan.

Membersihkan Spasi dan Mengambil Potongan #

Data yang diinput lewat formulir web sering membawa spasi liar di ujungnya. Keluarga trim membuangnya, dan keluarga substring mengambil potongan tengahnya:

Tanpa argumen kumpulan karakter, ltrim/rtrim/btrim membuang spasi; dengan argumen, fungsi-fungsi tersebut membuang setiap karakter yang tercantum di kumpulan itu. Pengambilan potongan teks dilakukan oleh substring dan sepupunya:

-- trim() menghapus spasi di kedua ujung teks
SELECT trim(both from '  teh melati  ') AS bersih;

-- varian satu sisi dan dengan kumpulan karakter tertentu
SELECT ltrim('xxKopi Gayo', 'x') AS sisi_kiri,
       rtrim('Teh Melatixx', 'x') AS sisi_kanan,
       btrim('xxGula Merahxx', 'x') AS dua_ujung;

-- substring() dan substr() mengambil potongan teks
SELECT substring('PostgreSQL' from 8) AS dari_posisi_8,
       substring('PostgreSQL', 1, 4) AS empat_karakter_awal,
       substr('susu kedelai 1 liter', 6, 7) AS substr_kedelai;

-- left() dan right() mengambil dari ujung
SELECT left('P101', 1) AS prefiks_kode,
       right('P101', 2) AS sufiks_kode;

-- rapikan data kotor: trim dikombinasikan dengan UPDATE
UPDATE produk SET nama = trim(both from nama) WHERE id = 2;

SELECT id, nama, length(nama) AS panjang_baru
FROM produk
ORDER BY id;

Output yang Dihasilkan:

   bersih   
------------
 teh melati
(1 row)

 sisi_kiri | sisi_kanan | dua_ujung  
-----------+------------+------------
 Kopi Gayo | Teh Melati | Gula Merah
(1 row)

 dari_posisi_8 | empat_karakter_awal | substr_kedelai 
---------------+---------------------+----------------
 SQL           | Post                | kedelai
(1 row)

 prefiks_kode | sufiks_kode 
--------------+-------------
 P            | 01
(1 row)

UPDATE 1
 id |         nama         | panjang_baru 
----+----------------------+--------------
  1 | Kopi Gayo            |            9
  2 | teh melati           |           10
  3 | GULA MERAH           |           10
  4 | susu kedelai 1 liter |           20
(4 rows)

Ada empat hal yang layak dibedah dari contoh di atas:

1. trim(both from ' teh melati '): kata kunci both from adalah bentuk eksplisit; trim(teks) tanpa kata kunci melakukan hal yang sama.
2. substring('PostgreSQL', 1, 4): pengambilan dimulai dari posisi 1 sepanjang 4 karakter — posisi karakter pertama di PostgreSQL adalah 1, bukan 0 seperti di beberapa bahasa pemrograman. substr() adalah padanan lawas dengan argumen posisi lalu panjang.
3. left('P101', 1) dan right('P101', 2): mengambil sejumlah karakter dari ujung kiri atau kanan — cara paling ringkas untuk memisahkan prefiks kode produk seperti huruf P dari P101.
4. UPDATE ... SET nama = trim(both from nama): pembersihan data yang sebenarnya — setelah pernyataan ini, length(nama) untuk 'teh melati' turun dari 14 menjadi 10 dan tabel tetap bersih untuk query berikutnya.

Menggabungkan Teks dan Perilaku NULL #

Menggabungkan teks di PostgreSQL punya dua jalur: fungsi concat/concat_ws dan operator ||. Keduanya tampak setara, tetapi perilakunya terhadap NULL sangat berbeda — dan perbedaan inilah yang paling sering menggagalkan laporan yang “tiba-tiba kosong”:

-- concat() memperlakukan NULL sebagai string kosong
SELECT concat('Budi', NULL, 'Siti') AS concat_dengan_null;

-- concat_ws() melewatkan NULL, pemisah tidak menjadi dobel
SELECT concat_ws(' - ', 'Budi', NULL, 'Siti') AS concat_ws_dengan_null;

-- operator || menularkan NULL ke seluruh hasil
SELECT 'Budi' || ' ' || 'Santoso' AS pipa_biasa,
       'Budi' || NULL AS pipa_dengan_null;

-- operator || juga menerima nilai non-teks di salah satu sisi
SELECT 'Rp ' || 5000 AS harga_sebagai_teks;

-- pemakaian nyata: membangun label dari kolom tabel
SELECT concat_ws(' ', 'Produk:', nama) AS label
FROM produk
WHERE id = 1;

Output yang Dihasilkan:

 concat_dengan_null 
--------------------
 BudiSiti
(1 row)

 concat_ws_dengan_null 
-----------------------
 Budi - Siti
(1 row)

  pipa_biasa  | pipa_dengan_null 
--------------+------------------
 Budi Santoso | 
(1 row)

 harga_sebagai_teks 
--------------------
 Rp 5000
(1 row)

       label       
-------------------
 Produk: Kopi Gayo
(1 row)

Operator || mengikuti aturan three-valued logic: operasi apa pun yang menyentuh NULL menghasilkan NULL, sehingga 'Budi' || NULL benar-benar kosong, bukan 'Budi'. Sebaliknya, concat dan concat_ws dirancang khusus untuk merangkai kolom yang boleh NULL — concat_ws bahkan tidak mencetak pemisah untuk elemen yang kosong, sehingga daftar yang dirangkai tidak meninggalkan sisa seperti 'Budi - - Siti'. Ketika Anda merangkai nilai yang mungkin NULL dan ingin hasil selalu berupa teks, pilih concat atau concat_ws; ketika Anda justru ingin NULL tetap menular (misalnya untuk mendeteksi data yang belum lengkap), operator || adalah pilihan yang jujur.

Mengganti, Menambah Pengisi, dan Membalik #

Kelompok terakhir dari fungsi string harian menangani tiga pekerjaan sisipan: mengganti potongan teks, menambah pengisi untuk mencapai panjang tertentu, dan memecah teks berdasarkan pemisah. Fungsi split_part khususnya sangat sering dipakai untuk memecah alamat email atau path:

-- replace() menukar setiap kemunculan potongan teks
SELECT replace('kopi susu gulaku', 'gulaku', 'gula aren') AS diganti;

-- lpad() dan rpad() menambah pengisi sampai panjang tertentu
SELECT lpad('7', 3, '0') AS nomor_berisi_nol,
       rpad('P101', 7, '.') AS kode_diberi_titik;

-- split_part() memecah teks lalu mengambil bagian ke-N
SELECT split_part('[email protected]', '@', 1) AS nama_pengguna,
       split_part('[email protected]', '@', 2) AS domain_email;

-- reverse() membalik urutan karakter
SELECT reverse('kasur') AS terbalik;

-- pad dipakai untuk merapikan tampilan kolom pendek
SELECT lpad(id::text, 4, '0') AS id_berisi_nol,
       rpad(nama, 18, '.') AS nama_rata_kiri
FROM produk
ORDER BY id;

Output yang Dihasilkan:

       diganti       
---------------------
 kopi susu gula aren
(1 row)

 nomor_berisi_nol | kode_diberi_titik 
------------------+-------------------
 007              | P101...
(1 row)

 nama_pengguna | domain_email 
---------------+--------------
 budi          | example.com
(1 row)

 terbalik 
----------
 rusak
(1 row)

 id_berisi_nol |   nama_rata_kiri   
---------------+--------------------
 0001          | Kopi Gayo.........
 0002          | teh melati........
 0003          | GULA MERAH........
 0004          | susu kedelai 1 lit
(4 rows)

Mari kita bedah bagian-bagian pentingnya:

1. replace('kopi susu gulaku', 'gulaku', 'gula aren'): semua kemunculan 'gulaku' ditukar — bila substring muncul tiga kali, ketiganya diganti. Ini bukan penggantian berbasis pola seperti regexp_replace; untuk pencocokan pola, ekspresi reguler dibahas tersendiri di section Full-Text Search dan pola LIKE di section Filtering dan Sorting.
2. lpad('7', 3, '0'): menambah karakter '0' di sisi kiri sampai total panjangnya 3, menghasilkan '007' — trik klasik untuk nomor invoice atau kode cabang.
3. split_part('[email protected]', '@', 2): memecah teks pada setiap @ lalu mengambil potongan kedua, example.com. Nomor potongan dihitung mulai dari 1.
4. lpad(id::text, 4, '0'): notasi id::text mengubah angka menjadi teks lebih dulu — lpad hanya menerima teks, dan ::text adalah type cast ringkas yang dibahas tuntas di chapter Fungsi Konversi Tipe Data .

Memformat dengan format() #

Ketika Anda perlu membangun satu teks dari banyak nilai dengan pola tetap, fungsi format() lebih ringkas dibanding rangkaian concat. Fungsinya mirip printf di bahasa C: string pola berisi penanda tempat, argumen menyusul di belakangnya. PostgreSQL mengenali penanda %s (nilai apa pun sebagai teks polos), %L (nilai dikutip sebagai literal SQL yang aman), dan %I (identifier dikutip sebagai nama kolom/tabel):

-- %s menyisipkan nilai apa pun sebagai teks polos
SELECT format('Pelanggan %s memesan %s item', 'Budi', 3) AS teks_polos;

-- %L mengutip nilai dengan benar agar aman sebagai literal SQL
SELECT format('SELECT * FROM pelanggan WHERE nama = %L;', 'Jum''at') AS teks_terkutip;

-- %s terhadap NULL menjadi string kosong,
-- sedangkan %L terhadap NULL menghasilkan teks 'NULL'
SELECT format('Catatan: %s', NULL) AS s_null_kosong,
       format('Catatan: %L', NULL) AS l_null_teks;

Output yang Dihasilkan:

          teks_polos           
-------------------------------
 Pelanggan Budi memesan 3 item
(1 row)

                  teks_terkutip                  
-------------------------------------------------
 SELECT * FROM pelanggan WHERE nama = 'Jum''at';
(1 row)

 s_null_kosong |  l_null_teks  
---------------+---------------
 Catatan:      | Catatan: NULL
(1 row)

Penanda %L melakukan pekerjaan pengutipan yang benar: nama 'Jum''at' yang memuat tanda kutip tunggal dihasilkan ulang sebagai 'Jum''at' — tanda kutip di dalam nilai digandakan sesuai aturan literal SQL, sehingga teks hasil format aman ditempelkan ke query lain. Perilaku terhadap NULL juga patut dicatat: %s merender NULL sebagai string kosong, sedangkan %L merendernya sebagai teks NULL tanpa kutip. Pola penanda ini akan sangat berguna nanti ketika kita menyusun query dinamis di dalam fungsi PL/pgSQL pada section Stored Programs.

Dengan perangkat string ini, data teks yang kotor bisa diukur, dibersihkan, dirangkai, dan diformat sepenuhnya di sisi database. Namun teks bukan satu-satunya warga kolom yang butuh olah raga — harga, stok, dan diskon menuntut fungsi matematika dengan aturan pembulatannya sendiri. Chapter berikutnya membedah keluarga Fungsi Numerik .

Referensi #

• String Functions and Operators
• Pattern Matching with format

CREATE TABLE produk (
    id    integer PRIMARY KEY,
    kode  varchar(10) NOT NULL,
    nama  text NOT NULL
);

INSERT INTO produk (id, kode, nama) VALUES
    (1, 'P101', 'Kopi Gayo'),
    (2, 'P102', '  teh melati  '),
    (3, 'P103', 'GULA MERAH'),
    (4, 'P104', 'susu kedelai 1 liter');

SELECT id, kode, nama FROM produk ORDER BY id;

-- %s menyisipkan nilai apa pun sebagai teks polos
SELECT format('Pelanggan %s memesan %s item', 'Budi', 3) AS teks_polos;

-- %L mengutip nilai dengan benar agar aman sebagai literal SQL
SELECT format('SELECT * FROM pelanggan WHERE nama = %L;', 'Jum''at') AS teks_terkutip;

-- %s terhadap NULL menjadi string kosong,
-- sedangkan %L terhadap NULL menghasilkan teks 'NULL'
SELECT format('Catatan: %s', NULL) AS s_null_kosong,
       format('Catatan: %L', NULL) AS l_null_teks;

-- concat() memperlakukan NULL sebagai string kosong
SELECT concat('Budi', NULL, 'Siti') AS concat_dengan_null;

-- concat_ws() melewatkan NULL, pemisah tidak menjadi dobel
SELECT concat_ws(' - ', 'Budi', NULL, 'Siti') AS concat_ws_dengan_null;

-- operator || menularkan NULL ke seluruh hasil
SELECT 'Budi' || ' ' || 'Santoso' AS pipa_biasa,
       'Budi' || NULL AS pipa_dengan_null;

-- operator || juga menerima nilai non-teks di salah satu sisi
SELECT 'Rp ' || 5000 AS harga_sebagai_teks;

-- pemakaian nyata: membangun label dari kolom tabel
SELECT concat_ws(' ', 'Produk:', nama) AS label
FROM produk
WHERE id = 1;

-- replace() menukar setiap kemunculan potongan teks
SELECT replace('kopi susu gulaku', 'gulaku', 'gula aren') AS diganti;

-- lpad() dan rpad() menambah pengisi sampai panjang tertentu
SELECT lpad('7', 3, '0') AS nomor_berisi_nol,
       rpad('P101', 7, '.') AS kode_diberi_titik;

-- split_part() memecah teks lalu mengambil bagian ke-N
SELECT split_part('[email protected]', '@', 1) AS nama_pengguna,
       split_part('[email protected]', '@', 2) AS domain_email;

-- reverse() membalik urutan karakter
SELECT reverse('kasur') AS terbalik;

-- pad dipakai untuk merapikan tampilan kolom pendek
SELECT lpad(id::text, 4, '0') AS id_berisi_nol,
       rpad(nama, 18, '.') AS nama_rata_kiri
FROM produk
ORDER BY id;

-- length() dan char_length() menghitung jumlah karakter,
-- spasi dihitung sebagai karakter biasa
SELECT nama,
       length(nama) AS panjang,
       char_length(nama) AS panjang_karakter
FROM produk
ORDER BY id;

-- tiga fungsi kapitalisasi
SELECT initcap('toko maju jaya') AS judul,
       upper('kopi gayo') AS huruf_besar,
       lower('GULA MERAH') AS huruf_kecil;

-- trim() menghapus spasi di kedua ujung teks
SELECT trim(both from '  teh melati  ') AS bersih;

-- varian satu sisi dan dengan kumpulan karakter tertentu
SELECT ltrim('xxKopi Gayo', 'x') AS sisi_kiri,
       rtrim('Teh Melatixx', 'x') AS sisi_kanan,
       btrim('xxGula Merahxx', 'x') AS dua_ujung;

-- substring() dan substr() mengambil potongan teks
SELECT substring('PostgreSQL' from 8) AS dari_posisi_8,
       substring('PostgreSQL', 1, 4) AS empat_karakter_awal,
       substr('susu kedelai 1 liter', 6, 7) AS substr_kedelai;

-- left() dan right() mengambil dari ujung
SELECT left('P101', 1) AS prefiks_kode,
       right('P101', 2) AS sufiks_kode;

-- rapikan data kotor: trim dikombinasikan dengan UPDATE
UPDATE produk SET nama = trim(both from nama) WHERE id = 2;

SELECT id, nama, length(nama) AS panjang_baru
FROM produk
ORDER BY id;