วิธีการแยกสตริงโดยใช้อักขระคั่นใน SQL Server

ในบทความนี้เราจะพูดถึงหลายวิธีในการแยกค่าสตริงที่คั่น สามารถทำได้โดยใช้หลายวิธีรวมถึง.

• การใช้ฟังก์ชัน STRING_SPLIT เพื่อแยกสตริง
• สร้างฟังก์ชันมูลค่าตารางที่ผู้ใช้กำหนดเพื่อแยกสตริง
• ใช้ XQuery เพื่อแยกค่าสตริงและแปลงสตริงที่ใช้ตัวคั่นเป็น XML

ก่อนอื่นเราต้องสร้างตารางและแทรกข้อมูลลงไปซึ่งจะใช้ทั้งสามวิธี ตารางควรมีแถวเดียวที่มีรหัสเขตข้อมูลและสตริงที่มีอักขระตัวคั่น สร้างตารางชื่อ“ student” โดยใช้รหัสต่อไปนี้

สร้างตารางนักเรียน (ID INT IDENTITY (1, 1), student_name VARCHAR (MAX))

แทรกชื่อนักเรียนโดยคั่นด้วยลูกน้ำในแถวเดียวโดยเรียกใช้รหัสต่อไปนี้

INSERT INTO student (student_name) VALUES ('Monroy, Montanez, Marolahakis, Negley, Albright, Garofolo, Pereira, Johnson, Wagner, Conrad')

ตรวจสอบว่าข้อมูลถูกแทรกลงในตารางหรือไม่โดยใช้รหัสต่อไปนี้

เลือก * จากนักเรียน

วิธีที่ 1: ใช้ฟังก์ชัน STRING_SPLIT เพื่อแยกสตริง

ใน SQL Server 2016 “ STRING_SPLIT” ได้รับการแนะนำฟังก์ชันซึ่งสามารถใช้ได้กับความเข้ากันได้ระดับ 130 ขึ้นไป ถ้าคุณใช้ SQL Server เวอร์ชัน 2016 หรือสูงกว่าคุณสามารถใช้ฟังก์ชัน build-in นี้ได้

นอกจากนี้ “ STRING_SPLIT” ป้อนสตริงที่มีสตริงย่อยที่คั่นและป้อนอักขระหนึ่งตัวเพื่อใช้เป็นตัวคั่นหรือตัวคั่น ฟังก์ชันจะแสดงผลตารางคอลัมน์เดียวซึ่งแถวนั้นมีสตริงย่อย ชื่อของคอลัมน์ผลลัพธ์คือ“มูลค่า”. ฟังก์ชันนี้ได้รับสองพารามิเตอร์ พารามิเตอร์ตัวแรกคือสตริงและพารามิเตอร์ตัวที่สองคือตัวคั่นหรือตัวคั่นตามที่เราต้องแยกสตริง เอาต์พุตประกอบด้วยตารางคอลัมน์เดียวซึ่งมีสตริงย่อยอยู่ คอลัมน์เอาต์พุตนี้มีชื่อว่า “ มูลค่า” ดังที่เราเห็นในรูปด้านล่าง ยิ่งไปกว่านั้น “ STRING SPLIT” ฟังก์ชัน table_valued ส่งคืนตารางว่างถ้าสตริงอินพุตเป็น NULL

ระดับความเข้ากันได้ของฐานข้อมูล:

แต่ละฐานข้อมูลเชื่อมต่อกันด้วยระดับความเข้ากันได้ ช่วยให้พฤติกรรมของฐานข้อมูลเข้ากันได้กับ SQL Server เวอร์ชันเฉพาะที่ทำงานอยู่

ตอนนี้เราจะเรียกฟังก์ชัน "string_split" เพื่อแยกสตริงที่คั่นด้วยเครื่องหมายจุลภาค แต่ระดับความเข้ากันได้น้อยกว่า 130 จึงเกิดข้อผิดพลาดตามมา "ชื่อวัตถุไม่ถูกต้อง" SPLIT_STRING ""

ดังนั้นเราต้องตั้งค่าระดับความเข้ากันได้ของฐานข้อมูลเป็น 130 หรือสูงกว่า ดังนั้นเราจะทำตามขั้นตอนเหล่านี้เพื่อตั้งค่าระดับความเข้ากันได้ของฐานข้อมูล

• ก่อนอื่นตั้งค่าฐานข้อมูลเป็น“ single_user_access_mode” โดยใช้รหัสต่อไปนี้

แก้ไขฐานข้อมูล <[ชื่อฐานข้อมูล]> ตั้งค่า SINGLE_USER

• ประการที่สองเปลี่ยนระดับความเข้ากันได้ของฐานข้อมูลโดยใช้รหัสต่อไปนี้

แก้ไขฐานข้อมูล <[database_name]> SET COMPATIBILITY_LEVEL = 130

• ทำให้ฐานข้อมูลกลับสู่โหมดการเข้าถึงหลายผู้ใช้โดยใช้รหัสต่อไปนี้

แก้ไขฐานข้อมูล <[ชื่อฐานข้อมูล]> ตั้งค่า MULTI_USER

ใช้ [หลัก] เปลี่ยนฐานข้อมูล [bridge_centrality] ตั้งค่า SINGLE_USER แก้ไขฐานข้อมูล [bridge_centrality] ตั้งค่า COMPATIBILITY_LEVEL = 130 แก้ไขฐานข้อมูล [bridge_centrality] ตั้งค่า MULTI_USER GO

ผลลัพธ์จะเป็น:

ตอนนี้เรียกใช้รหัสนี้เพื่อให้ได้ผลลัพธ์ที่ต้องการ

ประกาศ @string_value VARCHAR (MAX); SET @ string_value = 'Monroy, Montanez, Marolahakis, Negley, Albright, Garofolo, Pereira, Johnson, Wagner, Conrad' SELECT * FROM STRING_SPLIT (@string_value, ',')

ผลลัพธ์สำหรับแบบสอบถามนี้จะเป็น:

วิธีที่ 2: ในการแยกสตริงให้สร้างฟังก์ชันมูลค่าตารางที่กำหนดโดยผู้ใช้

แน่นอนว่าวิธีการดั้งเดิมนี้ได้รับการสนับสนุนโดย SQL Server ทุกเวอร์ชัน ในเทคนิคนี้เราจะสร้างฟังก์ชันที่ผู้ใช้กำหนดเองเพื่อแยกสตริงด้วยอักขระที่คั่นด้วยตัวคั่นโดยใช้ "การสมัคร” ฟังก์ชัน“CHARINDEX"และในขณะที่วนซ้ำ ฟังก์ชันนี้สามารถใช้เพื่อเพิ่มข้อมูลลงในตารางผลลัพธ์ได้เนื่องจากประเภทการส่งคืนคือ "ตาราง"

สร้างฟังก์ชัน [dbo] [split_string] (@string_value NVARCHAR (MAX), @delimiter_character CHAR (1)) RETURNS @result_set ตาราง (splited_data NVARCHAR (MAX)) BEGIN DECLARE @start_position INT, @ending_start INTABLE @ending_position = CHARINDEX (@delimiter_character, @string_value) WHILE @start_position 
ตอนนี้เรียกใช้สคริปต์ด้านล่างเพื่อเรียกใช้ฟังก์ชันแยกเพื่อแยกสตริงตามอักขระตัวคั่น
ประกาศ @student_name VARCHAR (MAX); ประกาศ @delimiter CHAR (1); SET @ delimiter = ',' SET @student_name = (SELECT student_name FROM student) SELECT * FROM dbo.split_string (@student_name, @delimiter)
ชุดผลลัพธ์จะเป็นแบบนี้
วิธีที่ 3: ใช้ XQuery เพื่อแยกค่าสตริงและแปลงสตริงที่ใช้ตัวคั่นเป็น XML
เนื่องจากฟังก์ชันที่ผู้ใช้กำหนดเองเป็นทรัพยากรที่ครบถ้วนดังนั้นเราจึงต้องหลีกเลี่ยงฟังก์ชันเหล่านี้ อีกทางเลือกหนึ่งคือฟังก์ชัน“ string_split” ในตัว แต่ฟังก์ชันนี้สามารถใช้สำหรับฐานข้อมูลที่ระดับความเข้ากันได้คือ 130 หรือสูงกว่า ดังนั้นนี่จึงเป็นอีกหนึ่งวิธีแก้ปัญหาที่ยากลำบากนี้ สามารถแยกสตริงได้โดยใช้สิ่งต่อไปนี้ XML.
ประกาศ @xml_value AS XML, @string_value AS VARCHAR (2000), @delimiter_value AS VARCHAR (15) SET @ string_value = (SELECT student_name FROM student) SET @delimiter_value = ',' SET @xml_value = Cast ((''+ แทนที่ (@string_value, @delimiter_value,'') + '') เป็น XML) SELECT @xml_value
ผลลัพธ์สำหรับแบบสอบถามนี้จะเป็น:
หากคุณต้องการดูไฟล์ XML ทั้งหมด คลิกที่ลิงค์ เมื่อคุณคลิกรหัสลิงก์จะมีลักษณะดังนี้
ตอนนี้สตริง XML ควรได้รับการประมวลผลเพิ่มเติม สุดท้ายเราจะใช้“ x-Query” เพื่อสืบค้นจาก XML
ประกาศ @xml_value AS XML, @string_value AS VARCHAR (2000), @delimiter_value AS VARCHAR (15) SET @ string_value = (SELECT student_name FROM student) SET @delimiter_value = ',' SET @xml_value = Cast ((''+ แทนที่ (@string_value, @delimiter_value,'') + '') เป็น XML) SELECT x.m.query ('. '). value ('. ',' VARCHAR (15) ') เป็นค่าจาก @ xml_value.nodes (' / studentname ') เป็น x (m) 
ผลลัพธ์จะเป็นดังนี้: