Hoe een string te splitsen door een scheidingsteken in SQL Server?

In dit artikel bespreken we verschillende manieren om de begrensde tekenreekswaarde te splitsen. Het kan worden bereikt met behulp van meerdere methoden, waaronder.

Gebruik van de STRING_SPLIT-functie om de string te splitsen
Maak een door de gebruiker gedefinieerde tabelwaardefunctie om de string te splitsen,
Gebruik XQuery om de tekenreekswaarde te splitsen en een tekenreeks met scheidingstekens om te zetten in XML

Allereerst moeten we een tabel maken en er gegevens in invoegen die in alle drie de methoden zullen worden gebruikt. De tabel moet een enkele rij bevatten met veld-id en een tekenreeks met scheidingstekens erin. Maak een tabel met de naam "student" met de volgende code.

CREATE TABLE student (ID INT IDENTITY (1, 1), student_name VARCHAR (MAX))

Voeg studentnamen in, gescheiden door komma's, in een enkele rij door de volgende code uit te voeren.

INVOEGEN IN student (student_name) WAARDEN ('Monroy, Montanez, Marolahakis, Negley, Albright, Garofolo, Pereira, Johnson, Wagner, Conrad')

Controleer of er gegevens in de tabel zijn ingevoegd of gebruik de volgende code niet.

selecteer * van leerling

Methode 1: gebruik de functie STRING_SPLIT om de tekenreeks te splitsen

In SQL Server 2016, "STRING_SPLIT" functie geïntroduceerd die kan worden gebruikt met compatibiliteitsniveau 130 en hoger. Als u de 2016 SQL Server-versie of hoger gebruikt, kunt u deze ingebouwde functie gebruiken.

Verder "STRING_SPLIT" voert een tekenreeks in die subtekenreeksen heeft en voert één teken in om als scheidingsteken of scheidingsteken te gebruiken. De functie voert een tabel met één kolom uit waarvan de rijen de subtekenreeksen bevatten. De naam van de uitvoerkolom is "Waarde". Deze functie krijgt twee parameters. De eerste parameter is een string en de tweede is een scheidingsteken of scheidingsteken op basis waarvan we de string moeten splitsen. De uitvoer bevat een tabel met één kolom waarin subtekenreeksen aanwezig zijn. Deze uitvoerkolom heet "Waarde" zoals we kunnen zien in de onderstaande afbeelding. Bovendien is de "STRING SPLIT" table_valued functie retourneert een lege tabel als de invoertekenreeks NULL is.

Compatibiliteitsniveau van de database:

Elke database is verbonden met een compatibiliteitsniveau. Het zorgt ervoor dat het gedrag van de database compatibel is met de specifieke SQL Server-versie waarop deze wordt uitgevoerd.

Nu zullen we de functie "string_split" aanroepen om een string te splitsen die wordt gescheiden door komma's. Maar het compatibiliteitsniveau was minder dan 130, vandaar dat de volgende fout werd verhoogd. 'Ongeldige objectnaam ‘SPLIT_STRING' '

Daarom moeten we het compatibiliteitsniveau van de database instellen op 130 of hoger. We zullen deze stap dus volgen om het compatibiliteitsniveau van de database in te stellen.

Stel eerst de database in op “single_user_access_mode” door de volgende code te gebruiken.

ALTER DATABASE <[database_name]> SET SINGLE_USER

Wijzig ten tweede het compatibiliteitsniveau van de database door de volgende code te gebruiken.

ALTER DATABASE <[databasenaam]> SET COMPATIBILITY_LEVEL = 130

Zet de database terug in de toegangsmodus voor meerdere gebruikers door de volgende code te gebruiken.

ALTER DATABASE <[databasenaam]> SET MULTI_USER

GEBRUIK [master] GO ALTER DATABASE [bridge_centrality] SET SINGLE_USER ALTER DATABASE [bridge_centrality] SET COMPATIBILITY_LEVEL = 130 ALTER DATABASE [bridge_centrality] SET MULTI_USER GO

De output zal zijn:

Voer nu deze code uit om het vereiste resultaat te krijgen.

DECLARE @string_waarde VARCHAR(MAX) ; SET @ string_value = 'Monroy, Montanez, Marolahakis, Negley, Albright, Garofolo, Pereira, Johnson, Wagner, Conrad' SELECT * FROM STRING_SPLIT (@string_value, ',')

De uitvoer voor deze zoekopdracht is:

Methode 2: Om de tekenreeks te splitsen, maakt u een door de gebruiker gedefinieerde functie met tabelwaarde

Deze traditionele methode wordt zeker ondersteund door alle versies van SQL Server. In deze techniek zullen we een door de gebruiker gedefinieerde functie maken om de string te splitsen door een scheidingsteken met behulp van “ONDERGROND"Functie,"CHARINDEX” en while-lus. Deze functie kan worden gebruikt om gegevens aan de uitvoertabel toe te voegen, aangezien het retourtype "tabel" is.

FUNCTIE CREËREN [dbo]. [Split_string] (@string_value NVARCHAR (MAX), @delimiter_character CHAR (1)) RETURNS @result_set TABLE (splited_data NVARCHAR (MAX)) BEGIN DECLARE @start_position INT, @ending_position INT SELECT @start_position = 1, @ending_position = CHARINDEX (@delimiter_character, @string_value) WHILE @start_position Voer nu het onderstaande script uit om een split-functie aan te roepen om string op scheidingsteken te splitsen.
DECLARE @student_name VARCHAR (MAX); DECLARE @delimiter CHAR (1); SET @ delimiter = ',' SET @student_name = (SELECT student_name FROM student) SELECT * FROM dbo.split_string (@student_name, @delimiter)
De resultaatset zal er zo uitzien.
Methode 3: Gebruik XQuery om de tekenreekswaarde te splitsen en een tekenreeks met scheidingstekens om te zetten in XML
Aangezien door de gebruiker gedefinieerde functies uitputtend zijn in de bronnen, moeten we deze functies vermijden. Een andere optie is de ingebouwde functie "string_split", maar deze functie kan worden gebruikt voor databases waarvan het compatibiliteitsniveau 130 of hoger is. Dus hier komt een andere oplossing om deze moeilijke taak op te lossen. Een string kan als volgt worden gesplitst XML.
DECLARE @xml_value AS XML, @string_value AS VARCHAR (2000), @delimiter_value AS VARCHAR (15) SET @ string_value = (SELECTEER studentnaam VAN student) SET @delimiter_value = ',' SET @xml_value = Cast ((''+ Vervangen (@string_value, @delimiter_value,'') + '') AS XML) SELECTEER @xml_value
De output voor deze query is:
Als u het hele XML-bestand wilt bekijken. Klik op de link. Als je eenmaal hebt geklikt, ziet de linkcode er als volgt uit.
Nu moet de XML-string verder worden verwerkt. Ten slotte zullen we “x-Query” gebruiken om te zoeken vanuit de XML.
DECLARE @xml_value AS XML, @string_value AS VARCHAR (2000), @delimiter_value AS VARCHAR (15) SET @ string_value = (SELECTEER studentnaam VAN student) SET @delimiter_value = ',' SET @xml_value = Cast ((''+ Vervangen (@string_value, @delimiter_value,'') + '') AS XML) SELECTEER x.m.query ('. '). Waarde ('. ',' VARCHAR (15) ') ALS WAARDE VAN @ xml_value.nodes (' / studentnaam ') AS x (m) 
De output zal als volgt zijn: