STRING_SPLIT (Transact-SQL)
Berlaku untuk:
SQL Server 2016 (13.x) dan yang lebih baru Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics
Fungsi bernilai tabel yang membagi string menjadi baris substring, berdasarkan karakter pemisah yang ditentukan.
Tingkat kompatibilitas 130
STRING_SPLIT memerlukan tingkat kompatibilitas setidaknya 130. Ketika tingkat kurang dari 130, SQL Server tidak dapat menemukan fungsi STRING_SPLIT.
Untuk mengubah tingkat kompatibilitas database, lihat Menampilkan atau Mengubah Tingkat Kompatibilitas Database.
Catatan
Konfigurasi kompatibilitas tidak diperlukan untuk STRING_SPLIT di Azure Synapse Analytics.
Sintaks
STRING_SPLIT ( string , separator [ , enable_ordinal ] )
Argumen
string
Adalah ekspresi jenis karakter apa pun (misalnya, nvarchar, varchar, nchar, atau char).
pemisah
Adalah ekspresi karakter tunggal dari jenis karakter apa pun (misalnya, nvarchar(1), varchar(1), nchar(1), atau char(1)) yang digunakan sebagai pemisah untuk substring yang digabungkan.
enable_ordinal
Ekspresi int atau bit yang berfungsi sebagai bendera untuk mengaktifkan atau menonaktifkan ordinal kolom output. Nilai 1 memungkinkan ordinal kolom. Jika enable_ordinal dihilangkan, NULL, atau memiliki nilai 0, kolom dinonaktifkan ordinal .
Catatan
Kolom argumen dan ordinal output enable_ordinal saat ini didukung di Azure SQL Database, Azure SQL Managed Instance, dan Azure Synapse Analytics (hanya kumpulan SQL tanpa server). Dimulai dengan Pratinjau SQL Server 2022 (16.x), kolom argumen dan output tersedia di SQL Server.
Jenis Kembalian
ordinal Jika kolom output tidak diaktifkan, STRING_SPLIT mengembalikan tabel kolom tunggal yang barisnya adalah substring. Nama kolom adalah value. Ini mengembalikan nvarchar jika salah satu argumen input adalah nvarchar atau nchar. Jika tidak, ia mengembalikan varchar. Panjang jenis pengembalian sama dengan panjang argumen string .
Jika argumen enable_ordinal diteruskan nilai 1, kolom kedua bernama ordinal dikembalikan yang terdiri dari nilai indeks berbasis 1 dari setiap posisi substring dalam string input. Jenis pengembalian adalah bigint.
Keterangan
STRING_SPLIT memasukkan string yang memiliki substring berbatas dan memasukkan satu karakter untuk digunakan sebagai pemisah atau pemisah. Secara opsional, fungsi mendukung argumen ketiga dengan nilai 0 atau 1 yang masing-masing ordinal menonaktifkan atau mengaktifkan kolom output.
STRING_SPLIT menghasilkan tabel kolom tunggal atau kolom ganda, bergantung pada argumen enable_ordinal .
Jika enable_ordinal adalah , dihilangkan
NULL, atau memiliki nilai 0, STRING_SPLIT mengembalikan tabel kolom tunggal yang barisnya berisi substring. Nama kolom output adalahvalue.Jika enable_ordinal memiliki nilai 1, fungsi mengembalikan tabel dua kolom, termasuk
ordinalkolom yang terdiri dari nilai indeks berbasis 1 dari substring dalam string input asli.
Perhatikan bahwa argumen enable_ordinal harus berupa nilai konstanta, bukan kolom atau variabel. Ini juga harus berupa jenis data bit atau int dengan nilai 0 atau 1. Jika tidak, fungsi akan memunculkan kesalahan.
Baris output mungkin dalam urutan apa pun. Urutan tidak dijamin cocok dengan urutan substring dalam string input. Anda dapat mengambil alih urutan pengurutan akhir dengan menggunakan klausa ORDER BY pada pernyataan SELECT, misalnya, ORDER BY value atau ORDER BY ordinal.
0x0000 (char(0)) adalah karakter yang tidak terdefinisi dalam kolase Windows dan tidak dapat disertakan dalam STRING_SPLIT.
Substring panjang nol kosong ada ketika string input berisi dua atau lebih kemunculan berturut-turut dari karakter pemisah. Substring kosong diperlakukan sama seperti substring biasa. Anda dapat memfilter baris apa pun yang berisi substring kosong dengan menggunakan klausa WHERE, misalnya WHERE value <> ''. Jika string input adalah NULL, fungsi STRING_SPLIT bernilai tabel mengembalikan tabel kosong.
Sebagai contoh, pernyataan SELECT berikut menggunakan karakter spasi sebagai pemisah:
SELECT value FROM STRING_SPLIT('Lorem ipsum dolor sit amet.', ' ');
Dalam eksekusi praktik, SELECT sebelumnya mengembalikan tabel hasil berikut:
| nilai |
|---|
| Lorem |
| Ipsum |
| dolor |
| sit |
| amet. |
Contoh berikut mengaktifkan ordinal kolom dengan meneruskan 1 untuk argumen ketiga opsional:
SELECT * FROM STRING_SPLIT('Lorem ipsum dolor sit amet.', ' ', 1);
Pernyataan ini kemudian mengembalikan tabel hasil berikut:
| nilai | Ordinal |
|---|---|
| Lorem | 1 |
| Ipsum | 2 |
| dolor | 3 |
| sit | 4 |
| amet. | 5 |
Contoh
A. Memisahkan string nilai yang dipisahkan koma
Uraikan daftar nilai yang dipisahkan koma dan kembalikan semua token yang tidak kosong:
DECLARE @tags NVARCHAR(400) = 'clothing,road,,touring,bike'
SELECT value
FROM STRING_SPLIT(@tags, ',')
WHERE RTRIM(value) <> '';
STRING_SPLIT akan mengembalikan string kosong jika tidak ada apa pun di antara pemisah. Condition RTRIM(value) <> '' akan menghapus token kosong.
B. Memisahkan string nilai yang dipisahkan koma dalam kolom
Tabel produk memiliki kolom dengan daftar tag yang dipisahkan koma yang diperlihatkan dalam contoh berikut:
| ProductId | Nama | Tag |
|---|---|---|
| 1 | Sarung Tangan Full-Finger | pakaian, jalan, tur, sepeda |
| 2 | LL Headset | bike |
| 3 | Bingkai Gunung HL | sepeda, gunung |
Kueri berikut mengubah setiap daftar tag dan menggabungkannya dengan baris asli:
SELECT ProductId, Name, value
FROM Product
CROSS APPLY STRING_SPLIT(Tags, ',');
Berikut adalah hasil yang ditetapkan.
| ProductId | Nama | nilai |
|---|---|---|
| 1 | Sarung Tangan Full-Finger | Pakaian |
| 1 | Sarung Tangan Full-Finger | jalan |
| 1 | Sarung Tangan Full-Finger | Tur |
| 1 | Sarung Tangan Full-Finger | bike |
| 2 | LL Headset | bike |
| 3 | Bingkai Gunung HL | bike |
| 3 | Bingkai Gunung HL | Gunung |
Catatan
Urutan output dapat bervariasi karena urutan tidak dijamin cocok dengan urutan substring dalam string input.
C. Agregasi menurut nilai
Pengguna harus membuat laporan yang menunjukkan jumlah produk per setiap tag, dipesan berdasarkan jumlah produk, dan untuk memfilter hanya tag dengan lebih dari dua produk.
SELECT value as tag, COUNT(*) AS [number_of_articles]
FROM Product
CROSS APPLY STRING_SPLIT(Tags, ',')
GROUP BY value
HAVING COUNT(*) > 2
ORDER BY COUNT(*) DESC;
D. Cari menurut nilai tag
Pengembang harus membuat kueri yang menemukan artikel menurut kata kunci. Mereka bisa menggunakan kueri berikut:
Untuk menemukan produk dengan satu tag (pakaian):
SELECT ProductId, Name, Tags
FROM Product
WHERE 'clothing' IN (SELECT value FROM STRING_SPLIT(Tags, ','));
Temukan produk dengan dua tag tertentu (pakaian dan jalan):
SELECT ProductId, Name, Tags
FROM Product
WHERE EXISTS (SELECT *
FROM STRING_SPLIT(Tags, ',')
WHERE value IN ('clothing', 'road'));
E. Menemukan baris menurut daftar nilai
Pengembang harus membuat kueri yang menemukan artikel menurut daftar ID. Mereka bisa menggunakan kueri berikut:
SELECT ProductId, Name, Tags
FROM Product
JOIN STRING_SPLIT('1,2,3',',')
ON value = ProductId;
Penggunaan STRING_SPLIT sebelumnya adalah pengganti anti-pola umum. Anti-pola seperti itu dapat melibatkan pembuatan string SQL dinamis di lapisan aplikasi atau di Transact-SQL. Atau anti-pola dapat dicapai dengan menggunakan operator LIKE. Lihat contoh pernyataan SELECT berikut:
SELECT ProductId, Name, Tags
FROM Product
WHERE ',1,2,3,' LIKE '%,' + CAST(ProductId AS VARCHAR(20)) + ',%';
F. Menemukan baris menurut nilai ordinal
Pernyataan berikut menemukan semua baris dengan nilai indeks genap:
SELECT *
FROM STRING_SPLIT('Austin,Texas,Seattle,Washington,Denver,Colorado', ',', 1)
WHERE ordinal % 2 = 0;
Pernyataan di atas mengembalikan tabel berikut:
| nilai | Ordinal |
|---|---|
| Texas | 2 |
| Washington | 4 |
| Colorado | 6 |
G. Mengurutkan baris menurut nilai ordinal
Pernyataan berikut mengembalikan nilai substring terpisah dari string input dan nilai ordinalnya, yang diurutkan menurut ordinal kolom:
SELECT * FROM STRING_SPLIT('E-D-C-B-A', '-', 1) ORDER BY ordinal DESC;
Pernyataan di atas mengembalikan tabel berikut:
| nilai | Ordinal |
|---|---|
| A | 5 |
| B | 4 |
| C | 3 |
| D | 2 |
| E | 1 |