STRING_SPLIT (T-SQL)

  • Artikel

Berlaku untuk: SQL Server 2016 (13.x) dan yang lebih baru Azure SQL DatabaseAzure SQL Managed InstanceTitik akhir analitik Azure Synapse AnalyticsSQL di Microsoft FabricWarehouse di Microsoft Fabric

STRING_SPLIT adalah fungsi bernilai tabel yang membagi string menjadi baris substring, berdasarkan karakter pemisah tertentu.

Tingkat kompatibilitas 130

STRING_SPLIT memerlukan tingkat kompatibilitas setidaknya 130. Ketika tingkat kurang dari 130, Mesin Database tidak dapat menemukan STRING_SPLIT fungsi.

Untuk mengubah tingkat kompatibilitas database, lihat Menampilkan atau mengubah tingkat kompatibilitas database.

Catatan

Konfigurasi kompatibilitas tidak diperlukan untuk STRING_SPLIT di Azure Synapse Analytics.

Konvensi sintaks transact-SQL

Sintaks

STRING_SPLIT ( string , separator [ , enable_ordinal ] )

Argumen

string

Ekspresi jenis karakter apa pun (misalnya, nvarchar, varchar, nchar, atau char).

pemisah

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

Berlaku untuk: Azure SQL Database, Azure SQL Managed Instance, Azure Synapse Analytics (hanya kumpulan SQL tanpa server), dan SQL Server 2022 (16.x) dan versi yang lebih baru

Ekspresi int atau bityang berfungsi sebagai bendera untuk mengaktifkan atau menonaktifkan ordinal kolom output. Nilai 1 mengaktifkan ordinal kolom. Jika enable_ordinal dihilangkan, NULL, atau memiliki nilai 0, kolom dinonaktifkan ordinal .

Jenis yang dikembalikan

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 posisi setiap substring dalam string input. Jenis pengembaliannya besar.

Keterangan

STRING_SPLIT memasukkan string yang memiliki substring yang dibatasi dan memasukkan satu karakter untuk digunakan sebagai pemisah atau pemisah. Secara opsional, fungsi mendukung argumen ketiga dengan nilai 0 atau 1 yang menonaktifkan atau mengaktifkan, masing-masing, ordinal kolom output.

STRING_SPLIT menghasilkan tabel kolom tunggal atau kolom ganda, tergantung 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 adalah value.

  • Jika enable_ordinal memiliki nilai 1, fungsi mengembalikan tabel dua kolom, termasuk ordinal kolom yang terdiri dari nilai indeks berbasis 1 dari substring dalam string input asli.

Argumen enable_ordinal harus berupa nilai konstanta, bukan kolom atau variabel. Ini juga harus berupa jenis data bitatau int dengan nilai 0 atau 1. Jika tidak, fungsi akan menimbulkan 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 ORDER BY klausul pada SELECT pernyataan, 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 kosong panjang nol 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 WHERE klausa, misalnya WHERE value <> ''. Jika string input adalah NULL, STRING_SPLIT fungsi bernilai tabel mengembalikan tabel kosong.

Sebagai contoh, pernyataan berikut SELECT menggunakan karakter spasi sebagai pemisah:

SELECT value FROM STRING_SPLIT('Lorem ipsum dolor sit amet.', ' ');

Dalam eksekusi praktik, tabel hasil berikut yang dikembalikan sebelumnya SELECT :

value
Lorem
ipsum
dolor
sit
amet.

Contoh berikut mengaktifkan ordinal kolom dengan meneruskan 1 argumen ketiga opsional:

SELECT * FROM STRING_SPLIT('Lorem ipsum dolor sit amet.', ' ', 1);

Pernyataan ini kemudian mengembalikan tabel hasil berikut:

value ordinal
Lorem 1
ipsum 2
dolor 3
sit 4
amet. 5

Contoh

J. 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 mengembalikan string kosong jika tidak ada di antara pemisah. Kondisi RTRIM(value) <> '' 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 Full-Finger Gloves clothing,road,touring,bike
2 LL Headset bike
3 HL Mountain Frame bike,mountain

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 Full-Finger Gloves clothing
1 Full-Finger Gloves road
1 Full-Finger Gloves touring
1 Full-Finger Gloves bike
2 LL Headset bike
3 HL Mountain Frame bike
3 HL Mountain Frame mountain

Catatan

Urutan output mungkin 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, diurutkan 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 berdasarkan nilai tag

Pengembang harus membuat kueri yang menemukan artikel berdasarkan kata kunci. Mereka dapat 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 berdasarkan daftar ID. Mereka bisa menggunakan kueri berikut:

SELECT ProductId, Name, Tags
FROM Product
JOIN STRING_SPLIT('1,2,3',',')
    ON value = ProductId;

Penggunaan sebelumnya STRING_SPLIT adalah pengganti antipattern umum. Antipattern semacam itu dapat melibatkan pembuatan string SQL dinamis di lapisan aplikasi atau di Transact-SQL. Atau antipattern dapat dicapai dengan menggunakan LIKE operator. Lihat contoh SELECT pernyataan 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:

value 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 berdasarkan ordinal kolom:

SELECT * FROM STRING_SPLIT('E-D-C-B-A', '-', 1) ORDER BY ordinal DESC;

Pernyataan di atas mengembalikan tabel berikut:

value ordinal
A 5
B 4
C 3
D 2
E 1

Konten terkait