Ik heb een SQL Server 2008 R2-kolom met een tekenreeks die ik moet splitsen door een komma. Ik heb veel antwoorden op StackOverflow gezien, maar geen van hen werkt in R2. Ik heb ervoor gezorgd dat ik geselecteerde machtigingen heb voor alle voorbeelden van gesplitste functies. Alle hulp wordt zeer op prijs gesteld.
Antwoord 1, autoriteit 100%
Ik heb deze SQL eerder gebruikt, wat misschien voor u werkt:-
CREATE FUNCTION dbo.splitstring ( @stringToSplit VARCHAR(MAX) )
RETURNS
@returnList TABLE ([Name] [nvarchar] (500))
AS
BEGIN
DECLARE @name NVARCHAR(255)
DECLARE @pos INT
WHILE CHARINDEX(',', @stringToSplit) > 0
BEGIN
SELECT @pos = CHARINDEX(',', @stringToSplit)
SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)
INSERT INTO @returnList
SELECT @name
SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
END
INSERT INTO @returnList
SELECT @stringToSplit
RETURN
END
en om het te gebruiken:-
SELECT * FROM dbo.splitstring('91,12,65,78,56,789')
Antwoord 2, autoriteit 26%
Heeft iemand in plaats van recursieve CTE’s en while-loops een meer set-gebaseerde aanpak overwogen? Merk op dat deze functie is geschreven voor de vraag, die was gebaseerd op SQL Server 2008 en komma als scheidingsteken. In SQL Server 2016 en hoger (en in compatibiliteitsniveau 130 en hoger), STRING_SPLIT()is een betere optie.
CREATE FUNCTION dbo.SplitString
(
@List nvarchar(max),
@Delim nvarchar(255)
)
RETURNS TABLE
AS
RETURN ( SELECT [Value] FROM
(
SELECT [Value] = LTRIM(RTRIM(SUBSTRING(@List, [Number],
CHARINDEX(@Delim, @List + @Delim, [Number]) - [Number])))
FROM (SELECT Number = ROW_NUMBER() OVER (ORDER BY name)
FROM sys.all_columns) AS x WHERE Number <= LEN(@List)
AND SUBSTRING(@Delim + @List, [Number], DATALENGTH(@Delim)/2) = @Delim
) AS y
);
GO
Als u wilt voorkomen dat de lengte van de tekenreeks wordt beperkt tot <= het aantal rijen in sys.all_columns(9.980 in modelin SQL Server 2017 ; veel hoger in uw eigen gebruikersdatabases), kunt u andere benaderingen gebruiken om de getallen af te leiden, zoals het bouwen van uw eigen tabel met getallen . U kunt ook een recursieve CTE gebruiken in gevallen waarin u geen systeemtabellen kunt gebruiken of uw eigen tabellen kunt maken:
CREATE FUNCTION dbo.SplitString
(
@List nvarchar(max),
@Delim nvarchar(255)
)
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN ( WITH n(n) AS (SELECT 1 UNION ALL SELECT n+1
FROM n WHERE n <= LEN(@List))
SELECT [Value] = SUBSTRING(@List, n,
CHARINDEX(@Delim, @List + @Delim, n) - n)
FROM n WHERE n <= LEN(@List)
AND SUBSTRING(@Delim + @List, n, DATALENGTH(@Delim)/2) = @Delim
);
GO
Maar je moet OPTION (MAXRECURSION 0)(of MAXRECURSION <longest possible string length if < 32768>) aan de buitenste query toevoegen om om fouten te voorkomen met recursie voor strings > 100 tekens. Als dat ook geen goed alternatief is, zie dan dit antwoordzoals aangegeven in de opmerkingen.
(Het scheidingsteken moet ook NCHAR(<=1228)zijn. Onderzoek nog steeds waarom.)
Meer over gesplitste functies, waarom (en bewijs dat) terwijl lussen en recursieve CTE’s niet schalen, en betere alternatieven, als strings uit de applicatielaag worden gesplitst:
- http://www.sqlperformance.com/2012/ 07/t-sql-queries/split-strings
- http: //www.sqlperformance.com/2012/08/t-sql-queries/splitting-strings-now-with-less-t-sql
- https://sqlblog.org/ 2010/07/07/splitting-a-list-of-integers-another-roundup
Antwoord 3, autoriteit 23%
Eindelijk is het wachten voorbij in SQL Server 2016ze hebben de functie Split string geïntroduceerd: STRING_SPLIT
select * From STRING_SPLIT ('a,b', ',') cs
Alle andere methoden om strings te splitsen, zoals XML, Tally-tabel, while-lus, enz., zijn weggeblazen door deze STRING_SPLIT-functie.
Hier is een uitstekend artikel met prestatievergelijking: Verrassingen en veronderstellingen over prestaties: STRING_SPLIT
Antwoord 4, autoriteit 10%
De eenvoudigste manier om dit te doen is door het XML-formaat te gebruiken.
1. Tekenreeks converteren naar rijen zonder tabel
QUERY
DECLARE @String varchar(100) = 'String1,String2,String3'
-- To change ',' to any other delimeter, just change ',' to your desired one
DECLARE @Delimiter CHAR = ','
SELECT LTRIM(RTRIM(Split.a.value('.', 'VARCHAR(100)'))) 'Value'
FROM
(
SELECT CAST ('<M>' + REPLACE(@String, @Delimiter, '</M><M>') + '</M>' AS XML) AS Data
) AS A
CROSS APPLY Data.nodes ('/M') AS Split(a)
RESULTAAT
x---------x
| Value |
x---------x
| String1 |
| String2 |
| String3 |
x---------x
2. Converteren naar rijen uit een tabel die een ID hebben voor elke CSV-rij
BRONTABEL
x-----x--------------------------x
| Id | Value |
x-----x--------------------------x
| 1 | String1,String2,String3 |
| 2 | String4,String5,String6 |
x-----x--------------------------x
QUERY
-- To change ',' to any other delimeter, just change ',' before '</M><M>' to your desired one
DECLARE @Delimiter CHAR = ','
SELECT ID,LTRIM(RTRIM(Split.a.value('.', 'VARCHAR(100)'))) 'Value'
FROM
(
SELECT ID,CAST ('<M>' + REPLACE(VALUE, @Delimiter, '</M><M>') + '</M>' AS XML) AS Data
FROM TABLENAME
) AS A
CROSS APPLY Data.nodes ('/M') AS Split(a)
RESULTAAT
x-----x----------x
| Id | Value |
x-----x----------x
| 1 | String1 |
| 1 | String2 |
| 1 | String3 |
| 2 | String4 |
| 2 | String5 |
| 2 | String6 |
x-----x----------x
Antwoord 5, autoriteit 3%
Ik had een snelle manier nodig om de +4van een postcodete verwijderen.
UPDATE #Emails
SET ZIPCode = SUBSTRING(ZIPCode, 1, (CHARINDEX('-', ZIPCODE)-1))
WHERE ZIPCode LIKE '%-%'
Geen proces… geen UDF… slechts één strak klein inline commando dat doet wat het moet doen. Niet chique, niet elegant.
Verander het scheidingsteken indien nodig, enz., en het zal voor alles werken.
Antwoord 6, autoriteit 3%
als je vervangt
WHILE CHARINDEX(',', @stringToSplit) > 0
met
WHILE LEN(@stringToSplit) > 0
je kunt die laatste insert verwijderen na de while-lus!
CREATE FUNCTION dbo.splitstring ( @stringToSplit VARCHAR(MAX) )
RETURNS
@returnList TABLE ([Name] [nvarchar] (500))
AS
BEGIN
DECLARE @name NVARCHAR(255)
DECLARE @pos INT
WHILE LEN(@stringToSplit) > 0
BEGIN
SELECT @pos = CHARINDEX(',', @stringToSplit)
if @pos = 0
SELECT @pos = LEN(@stringToSplit)
SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)
INSERT INTO @returnList
SELECT @name
SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
END
RETURN
END
Antwoord 7, autoriteit 2%
De veelgebruikte aanpak met XML-elementen breekt in het geval van verboden tekens. Dit is een manier om deze methode met elk type teken te gebruiken, zelfs met de puntkomma als scheidingsteken.
De truc is om eerst SELECT SomeString AS [*] FOR XML PATH('')te gebruiken om alle verboden tekens correct te escapen. Dat is de reden waarom ik het scheidingsteken vervang door een magische waardeom problemen met ;als scheidingsteken te voorkomen.
DECLARE @Dummy TABLE (ID INT, SomeTextToSplit NVARCHAR(MAX))
INSERT INTO @Dummy VALUES
(1,N'A&B;C;D;E, F')
,(2,N'"C" & ''D'';<C>;D;E, F');
DECLARE @Delimiter NVARCHAR(10)=';'; --special effort needed (due to entities coding with "&code;")!
WITH Casted AS
(
SELECT *
,CAST(N'<x>' + REPLACE((SELECT REPLACE(SomeTextToSplit,@Delimiter,N'§§Split$me$here§§') AS [*] FOR XML PATH('')),N'§§Split$me$here§§',N'</x><x>') + N'</x>' AS XML) AS SplitMe
FROM @Dummy
)
SELECT Casted.ID
,x.value(N'.',N'nvarchar(max)') AS Part
FROM Casted
CROSS APPLY SplitMe.nodes(N'/x') AS A(x)
Het resultaat
ID Part
1 A&B
1 C
1 D
1 E, F
2 "C" & 'D'
2 <C>
2 D
2 E, F
Antwoord 8
Alle functies voor het splitsen van strings die een soort van Loop-ing (iteraties) gebruiken, presteren slecht. Ze moeten worden vervangen door een set-gebaseerde oplossing.
Deze code werkt uitstekend.
CREATE FUNCTION dbo.SplitStrings
(
@List NVARCHAR(MAX),
@Delimiter NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
SELECT Item = y.i.value('(./text())[1]', 'nvarchar(4000)')
FROM
(
SELECT x = CONVERT(XML, '<i>'
+ REPLACE(@List, @Delimiter, '</i><i>')
+ '</i>').query('.')
) AS a CROSS APPLY x.nodes('i') AS y(i)
);
GO
Antwoord 9
Ik moest onlangs zoiets schrijven. Hier is de oplossing die ik bedacht. Het is gegeneraliseerd voor elke tekenreeks met scheidingstekens en ik denk dat het iets beter zou presteren:
CREATE FUNCTION [dbo].[SplitString]
( @string nvarchar(4000)
, @delim nvarchar(100) )
RETURNS
@result TABLE
( [Value] nvarchar(4000) NOT NULL
, [Index] int NOT NULL )
AS
BEGIN
DECLARE @str nvarchar(4000)
, @pos int
, @prv int = 1
SELECT @pos = CHARINDEX(@delim, @string)
WHILE @pos > 0
BEGIN
SELECT @str = SUBSTRING(@string, @prv, @pos - @prv)
INSERT INTO @result SELECT @str, @prv
SELECT @prv = @pos + LEN(@delim)
, @pos = CHARINDEX(@delim, @string, @pos + 1)
END
INSERT INTO @result SELECT SUBSTRING(@string, @prv, 4000), @prv
RETURN
END
Antwoord 10
Als u een snelle ad-hocoplossing nodig heeft voor veelvoorkomende gevallen met een minimum aan code, dan zal deze recursieve CTE-two-liner het doen:
DECLARE @s VARCHAR(200) = ',1,2,,3,,,4,,,,5,'
;WITH
a AS (SELECT i=-1, j=0 UNION ALL SELECT j, CHARINDEX(',', @s, j + 1) FROM a WHERE j > i),
b AS (SELECT SUBSTRING(@s, i+1, IIF(j>0, j, LEN(@s)+1)-i-1) s FROM a WHERE i >= 0)
SELECT * FROM b
Gebruik dit als een op zichzelf staande instructie of voeg gewoon de bovenstaande CTE’s toe aan een van uw zoekopdrachten en u kunt de resulterende tabel bsamenvoegen met andere voor gebruik in eventuele verdere expressies.
bewerken (door Shnugo)
Als u een teller toevoegt, krijgt u een positie-index samen met de lijst:
DECLARE @s VARCHAR(200) = '1,2333,344,4'
;WITH
a AS (SELECT n=0, i=-1, j=0 UNION ALL SELECT n+1, j, CHARINDEX(',', @s, j+1) FROM a WHERE j > i),
b AS (SELECT n, SUBSTRING(@s, i+1, IIF(j>0, j, LEN(@s)+1)-i-1) s FROM a WHERE i >= 0)
SELECT * FROM b;
Het resultaat:
n s
1 1
2 2333
3 344
4 4
Antwoord 11
Een oplossing met behulp van een CTE, als iemand dat nodig zou hebben (behalve ik, die dat duidelijk deed, daarom heb ik het geschreven).
declare @StringToSplit varchar(100) = 'Test1,Test2,Test3';
declare @SplitChar varchar(10) = ',';
with StringToSplit as (
select
ltrim( rtrim( substring( @StringToSplit, 1, charindex( @SplitChar, @StringToSplit ) - 1 ) ) ) Head
, substring( @StringToSplit, charindex( @SplitChar, @StringToSplit ) + 1, len( @StringToSplit ) ) Tail
union all
select
ltrim( rtrim( substring( Tail, 1, charindex( @SplitChar, Tail ) - 1 ) ) ) Head
, substring( Tail, charindex( @SplitChar, Tail ) + 1, len( Tail ) ) Tail
from StringToSplit
where charindex( @SplitChar, Tail ) > 0
union all
select
ltrim( rtrim( Tail ) ) Head
, '' Tail
from StringToSplit
where charindex( @SplitChar, Tail ) = 0
and len( Tail ) > 0
)
select Head from StringToSplit
Antwoord 12
Dit is meer op maat gemaakt. Wanneer ik dit doe, heb ik meestal een door komma’s gescheiden lijst met unieke id’s (INT of BIGINT), die ik als een tabel wil casten om te gebruiken als een inner join naar een andere tabel met een primaire sleutel van INT of BIGINT. Ik wil een in-line tabelwaardefunctie teruggeven, zodat ik de meest efficiënte join mogelijk heb.
Voorbeeld van gebruik zou zijn:
DECLARE @IDs VARCHAR(1000);
SET @IDs = ',99,206,124,8967,1,7,3,45234,2,889,987979,';
SELECT me.Value
FROM dbo.MyEnum me
INNER JOIN dbo.GetIntIdsTableFromDelimitedString(@IDs) ids ON me.PrimaryKey = ids.ID
Ik heb het idee gestolen van http://sqlrecords .blogspot.com/2012/11/converting-delimited-list-to-table.html, wijzigen naar in-line tabelwaarde en casten als INT.
create function dbo.GetIntIDTableFromDelimitedString
(
@IDs VARCHAR(1000) --this parameter must start and end with a comma, eg ',123,456,'
--all items in list must be perfectly formatted or function will error
)
RETURNS TABLE AS
RETURN
SELECT
CAST(SUBSTRING(@IDs,Nums.number + 1,CHARINDEX(',',@IDs,(Nums.number+2)) - Nums.number - 1) AS INT) AS ID
FROM
[master].[dbo].[spt_values] Nums
WHERE Nums.Type = 'P'
AND Nums.number BETWEEN 1 AND DATALENGTH(@IDs)
AND SUBSTRING(@IDs,Nums.number,1) = ','
AND CHARINDEX(',',@IDs,(Nums.number+1)) > Nums.number;
GO
Antwoord 13
Er is hier een correcte versie, maar ik dacht dat het leuk zou zijn om een beetje fouttolerantie toe te voegen voor het geval ze een komma hebben en het zo te maken dat je het niet als een functie kunt gebruiken, maar als onderdeel van een groter stukje code. Voor het geval je het maar één keer gebruikt en geen functie nodig hebt. Dit is ook voor gehele getallen (daar had ik het voor nodig), dus het kan zijn dat je je gegevenstypen moet wijzigen.
DECLARE @StringToSeperate VARCHAR(10)
SET @StringToSeperate = '1,2,5'
--SELECT @StringToSeperate IDs INTO #Test
DROP TABLE #IDs
CREATE TABLE #IDs (ID int)
DECLARE @CommaSeperatedValue NVARCHAR(255) = ''
DECLARE @Position INT = LEN(@StringToSeperate)
--Add Each Value
WHILE CHARINDEX(',', @StringToSeperate) > 0
BEGIN
SELECT @Position = CHARINDEX(',', @StringToSeperate)
SELECT @CommaSeperatedValue = SUBSTRING(@StringToSeperate, 1, @Position-1)
INSERT INTO #IDs
SELECT @CommaSeperatedValue
SELECT @StringToSeperate = SUBSTRING(@StringToSeperate, @Position+1, LEN(@StringToSeperate)-@Position)
END
--Add Last Value
IF (LEN(LTRIM(RTRIM(@StringToSeperate)))>0)
BEGIN
INSERT INTO #IDs
SELECT SUBSTRING(@StringToSeperate, 1, @Position)
END
SELECT * FROM #IDs
Antwoord 14
Ik heb de functie van +Andy Robinson een beetje aangepast. Nu kunt u alleen het vereiste onderdeel uit de terugkerende tabel selecteren:
CREATE FUNCTION dbo.splitstring ( @stringToSplit VARCHAR(MAX) )
RETURNS
@returnList TABLE ([numOrder] [tinyint] , [Name] [nvarchar] (500)) AS
BEGIN
DECLARE @name NVARCHAR(255)
DECLARE @pos INT
DECLARE @orderNum INT
SET @orderNum=0
WHILE CHARINDEX('.', @stringToSplit) > 0
BEGIN
SELECT @orderNum=@orderNum+1;
SELECT @pos = CHARINDEX('.', @stringToSplit)
SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)
INSERT INTO @returnList
SELECT @orderNum,@name
SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
END
SELECT @orderNum=@orderNum+1;
INSERT INTO @returnList
SELECT @orderNum, @stringToSplit
RETURN
END
Usage:
SELECT Name FROM dbo.splitstring('ELIS.YD.CRP1.1.CBA.MDSP.T389.BT') WHERE numOrder=5
Antwoord 15
Ik neem de xml-route door de waarden in elementen te wikkelen (M maar alles werkt):
declare @v nvarchar(max) = '100,201,abcde'
select
a.value('.', 'varchar(max)')
from
(select cast('<M>' + REPLACE(@v, ',', '</M><M>') + '</M>' AS XML) as col) as A
CROSS APPLY A.col.nodes ('/M') AS Split(a)
Antwoord 16
hier is een versie die een patroon kan splitsen met behulp van patindex, een eenvoudige aanpassing van de post hierboven. Ik had een geval waarin ik een string moest splitsen die meerdere scheidingstekens bevatte.
alter FUNCTION dbo.splitstring ( @stringToSplit VARCHAR(1000), @splitPattern varchar(10) )
RETURNS
@returnList TABLE ([Name] [nvarchar] (500))
AS
BEGIN
DECLARE @name NVARCHAR(255)
DECLARE @pos INT
WHILE PATINDEX(@splitPattern, @stringToSplit) > 0
BEGIN
SELECT @pos = PATINDEX(@splitPattern, @stringToSplit)
SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)
INSERT INTO @returnList
SELECT @name
SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
END
INSERT INTO @returnList
SELECT @stringToSplit
RETURN
END
select * from dbo.splitstring('stringa/stringb/x,y,z','%[/,]%');
resultaat ziet er zo uit
stringa
stringb
x
ja
z
Antwoord 17
Persoonlijk gebruik ik deze functie :
ALTER FUNCTION [dbo].[CUST_SplitString]
(
@String NVARCHAR(4000),
@Delimiter NCHAR(1)
)
RETURNS TABLE
AS
RETURN
(
WITH Split(stpos,endpos)
AS(
SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
UNION ALL
SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)
FROM Split
WHERE endpos > 0
)
SELECT 'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
'Data' = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)
FROM Split
)
Antwoord 18
Ik heb een dubbele splitter ontwikkeld (neemt twee gesplitste tekens) zoals gevraagd Hier. Het zou van enige waarde kunnen zijn in deze thread, aangezien dit het meest wordt verwezen voor vragen met betrekking tot het splitsen van strings.
CREATE FUNCTION uft_DoubleSplitter
(
-- Add the parameters for the function here
@String VARCHAR(4000),
@Splitter1 CHAR,
@Splitter2 CHAR
)
RETURNS @Result TABLE (Id INT,MId INT,SValue VARCHAR(4000))
AS
BEGIN
DECLARE @FResult TABLE(Id INT IDENTITY(1, 1),
SValue VARCHAR(4000))
DECLARE @SResult TABLE(Id INT IDENTITY(1, 1),
MId INT,
SValue VARCHAR(4000))
SET @String = @String+@Splitter1
WHILE CHARINDEX(@Splitter1, @String) > 0
BEGIN
DECLARE @WorkingString VARCHAR(4000) = NULL
SET @WorkingString = SUBSTRING(@String, 1, CHARINDEX(@Splitter1, @String) - 1)
--Print @workingString
INSERT INTO @FResult
SELECT CASE
WHEN @WorkingString = '' THEN NULL
ELSE @WorkingString
END
SET @String = SUBSTRING(@String, LEN(@WorkingString) + 2, LEN(@String))
END
IF ISNULL(@Splitter2, '') != ''
BEGIN
DECLARE @OStartLoop INT
DECLARE @OEndLoop INT
SELECT @OStartLoop = MIN(Id),
@OEndLoop = MAX(Id)
FROM @FResult
WHILE @OStartLoop <= @OEndLoop
BEGIN
DECLARE @iString VARCHAR(4000)
DECLARE @iMId INT
SELECT @iString = SValue+@Splitter2,
@iMId = Id
FROM @FResult
WHERE Id = @OStartLoop
WHILE CHARINDEX(@Splitter2, @iString) > 0
BEGIN
DECLARE @iWorkingString VARCHAR(4000) = NULL
SET @IWorkingString = SUBSTRING(@iString, 1, CHARINDEX(@Splitter2, @iString) - 1)
INSERT INTO @SResult
SELECT @iMId,
CASE
WHEN @iWorkingString = '' THEN NULL
ELSE @iWorkingString
END
SET @iString = SUBSTRING(@iString, LEN(@iWorkingString) + 2, LEN(@iString))
END
SET @OStartLoop = @OStartLoop + 1
END
INSERT INTO @Result
SELECT MId AS PrimarySplitID,
ROW_NUMBER() OVER (PARTITION BY MId ORDER BY Mid, Id) AS SecondarySplitID ,
SValue
FROM @SResult
END
ELSE
BEGIN
INSERT INTO @Result
SELECT Id AS PrimarySplitID,
NULL AS SecondarySplitID,
SValue
FROM @FResult
END
RETURN
Gebruik:
--FirstSplit
SELECT * FROM uft_DoubleSplitter('ValueA=ValueB=ValueC=ValueD==ValueE&ValueA=ValueB=ValueC===ValueE&ValueA=ValueB==ValueD===','&',NULL)
--Second Split
SELECT * FROM uft_DoubleSplitter('ValueA=ValueB=ValueC=ValueD==ValueE&ValueA=ValueB=ValueC===ValueE&ValueA=ValueB==ValueD===','&','=')
Mogelijk gebruik (krijg de tweede waarde van elke splitsing):
SELECT fn.SValue
FROM uft_DoubleSplitter('ValueA=ValueB=ValueC=ValueD==ValueE&ValueA=ValueB=ValueC===ValueE&ValueA=ValueB==ValueD===', '&', '=')AS fn
WHERE fn.mid = 2
Antwoord 19
Een recursieve op cte gebaseerde oplossing
declare @T table (iden int identity, col1 varchar(100));
insert into @T(col1) values
('ROOT/South America/Lima/Test/Test2')
, ('ROOT/South America/Peru/Test/Test2')
, ('ROOT//South America/Venuzuala ')
, ('RtT/South America / ')
, ('ROOT/South Americas// ');
declare @split char(1) = '/';
select @split as split;
with cte as
( select t.iden, case when SUBSTRING(REVERSE(rtrim(t.col1)), 1, 1) = @split then LTRIM(RTRIM(t.col1)) else LTRIM(RTRIM(t.col1)) + @split end as col1, 0 as pos , 1 as cnt
from @T t
union all
select t.iden, t.col1 , charindex(@split, t.col1, t.pos + 1), cnt + 1
from cte t
where charindex(@split, t.col1, t.pos + 1) > 0
)
select t1.*, t2.pos, t2.cnt
, ltrim(rtrim(SUBSTRING(t1.col1, t1.pos+1, t2.pos-t1.pos-1))) as bingo
from cte t1
join cte t2
on t2.iden = t1.iden
and t2.cnt = t1.cnt+1
and t2.pos > t1.pos
order by t1.iden, t1.cnt;
Antwoord 20
Met alle respect voor @AviG, dit is de bugvrije versie van de functie die door hem is ontwikkeld om alle tokens volledig terug te geven.
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'TF' AND name = 'TF_SplitString')
DROP FUNCTION [dbo].[TF_SplitString]
GO
-- =============================================
-- Author: AviG
-- Amendments: Parameterize the delimeter and included the missing chars in last token - Gemunu Wickremasinghe
-- Description: Tabel valued function that Breaks the delimeted string by given delimeter and returns a tabel having split results
-- Usage
-- select * from [dbo].[TF_SplitString]('token1,token2,,,,,,,,token969',',')
-- 969 items should be returned
-- select * from [dbo].[TF_SplitString]('4672978261,4672978255',',')
-- 2 items should be returned
-- =============================================
CREATE FUNCTION dbo.TF_SplitString
( @stringToSplit VARCHAR(MAX) ,
@delimeter char = ','
)
RETURNS
@returnList TABLE ([Name] [nvarchar] (500))
AS
BEGIN
DECLARE @name NVARCHAR(255)
DECLARE @pos INT
WHILE LEN(@stringToSplit) > 0
BEGIN
SELECT @pos = CHARINDEX(@delimeter, @stringToSplit)
if @pos = 0
BEGIN
SELECT @pos = LEN(@stringToSplit)
SELECT @name = SUBSTRING(@stringToSplit, 1, @pos)
END
else
BEGIN
SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)
END
INSERT INTO @returnList
SELECT @name
SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
END
RETURN
END
Antwoord 21
Dit is gebaseerd op het antwoord van Andy Robertson, ik had een ander scheidingsteken nodig dan een komma.
CREATE FUNCTION dbo.splitstring ( @stringToSplit nvarchar(MAX), @delim nvarchar(max))
RETURNS
@returnList TABLE ([value] [nvarchar] (MAX))
AS
BEGIN
DECLARE @value NVARCHAR(max)
DECLARE @pos INT
WHILE CHARINDEX(@delim, @stringToSplit) > 0
BEGIN
SELECT @pos = CHARINDEX(@delim, @stringToSplit)
SELECT @value = SUBSTRING(@stringToSplit, 1, @pos - 1)
INSERT INTO @returnList
SELECT @value
SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos + LEN(@delim), LEN(@stringToSplit) - @pos)
END
INSERT INTO @returnList
SELECT @stringToSplit
RETURN
END
GO
En om het te gebruiken:
SELECT * FROM dbo.splitstring('test1 test2 test3', ' ');
(Getest op SQL Server 2008 R2)
EDIT: correcte testcode
Antwoord 22
Eenvoudig
DECLARE @String varchar(100) = '11,21,84,85,87'
SELECT * FROM TB_PAPEL WHERE CD_PAPEL IN (SELECT value FROM STRING_SPLIT(@String, ','))
-- EQUIVALENTE
SELECT * FROM TB_PAPEL WHERE CD_PAPEL IN (11,21,84,85,87)
Antwoord 23
ALTER FUNCTION [dbo].func_split_string
(
@input as varchar(max),
@delimiter as varchar(10) = ";"
)
RETURNS @result TABLE
(
id smallint identity(1,1),
csv_value varchar(max) not null
)
AS
BEGIN
DECLARE @pos AS INT;
DECLARE @string AS VARCHAR(MAX) = '';
WHILE LEN(@input) > 0
BEGIN
SELECT @pos = CHARINDEX(@delimiter,@input);
IF(@pos<=0)
select @pos = len(@input)
IF(@pos <> LEN(@input))
SELECT @string = SUBSTRING(@input, 1, @pos-1);
ELSE
SELECT @string = SUBSTRING(@input, 1, @pos);
INSERT INTO @result SELECT @string
SELECT @input = SUBSTRING(@input, @pos+len(@delimiter), LEN(@input)-@pos)
END
RETURN
END
Antwoord 24
U kunt deze functie gebruiken:
CREATE FUNCTION SplitString
(
@Input NVARCHAR(MAX),
@Character CHAR(1)
)
RETURNS @Output TABLE (
Item NVARCHAR(1000)
)
AS
BEGIN
DECLARE @StartIndex INT, @EndIndex INT
SET @StartIndex = 1
IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character
BEGIN
SET @Input = @Input + @Character
END
WHILE CHARINDEX(@Character, @Input) > 0
BEGIN
SET @EndIndex = CHARINDEX(@Character, @Input)
INSERT INTO @Output(Item)
SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)
SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))
END
RETURN
END
GO
Antwoord 25
Hier is een voorbeeld dat u als functie kunt gebruiken of u kunt dezelfde logica in procedure zetten.
–SELECT * uit [dbo].fn_SplitString ;
CREATE FUNCTION [dbo].[fn_SplitString]
(@CSV VARCHAR(MAX), @Delimeter VARCHAR(100) = ',')
RETURNS @retTable TABLE
(
[value] VARCHAR(MAX) NULL
)AS
BEGIN
DECLARE
@vCSV VARCHAR (MAX) = @CSV,
@vDelimeter VARCHAR (100) = @Delimeter;
IF @vDelimeter = ';'
BEGIN
SET @vCSV = REPLACE(@vCSV, ';', '~!~#~');
SET @vDelimeter = REPLACE(@vDelimeter, ';', '~!~#~');
END;
SET @vCSV = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@vCSV, '&', '&'), '<', '<'), '>', '>'), '''', '''), '"', '"');
DECLARE @xml XML;
SET @xml = '<i>' + REPLACE(@vCSV, @vDelimeter, '</i><i>') + '</i>';
INSERT INTO @retTable
SELECT
x.i.value('.', 'varchar(max)') AS COLUMNNAME
FROM @xml.nodes('//i')AS x(i);
RETURN;
END;
Antwoord 26
/*
Antwoord op T-SQL gesplitste tekenreeks
Gebaseerd op antwoorden van Andy Robinsonen AviG
Verbeterde functionaliteit ref: LEN-functie zonder volgspaties in SQL Server
Dit ‘bestand’ moet geldig zijn als zowel een markdown-bestand als een SQL-bestand
*/
CREATE FUNCTION dbo.splitstring ( --CREATE OR ALTER
@stringToSplit NVARCHAR(MAX)
) RETURNS @returnList TABLE ([Item] NVARCHAR (MAX))
AS BEGIN
DECLARE @name NVARCHAR(MAX)
DECLARE @pos BIGINT
SET @stringToSplit = @stringToSplit + ',' -- this should allow entries that end with a `,` to have a blank value in that "column"
WHILE ((LEN(@stringToSplit+'_') > 1)) BEGIN -- `+'_'` gets around LEN trimming terminal spaces. See URL referenced above
SET @pos = COALESCE(NULLIF(CHARINDEX(',', @stringToSplit),0),LEN(@stringToSplit+'_')) -- COALESCE grabs first non-null value
SET @name = SUBSTRING(@stringToSplit, 1, @pos-1) --MAX size of string of type nvarchar is 4000
SET @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, 4000) -- With SUBSTRING fn (MS web): "If start is greater than the number of characters in the value expression, a zero-length expression is returned."
INSERT INTO @returnList SELECT @name --additional debugging parameters below can be added
-- + ' pos:' + CAST(@pos as nvarchar) + ' remain:''' + @stringToSplit + '''(' + CAST(LEN(@stringToSplit+'_')-1 as nvarchar) + ')'
END
RETURN
END
GO
/*
Testcases: zie de URL waarnaar hierboven wordt verwezen als ‘verbeterde functionaliteit’
SELECT *,LEN(Item+'_')-1 'L' from splitstring('a,,b')
Item | L
--- | ---
a | 1
| 0
b | 1
SELECT *,LEN(Item+'_')-1 'L' from splitstring('a,,')
Item | L
--- | ---
a | 1
| 0
| 0
SELECT *,LEN(Item+'_')-1 'L' from splitstring('a,, ')
Item | L
--- | ---
a | 1
| 0
| 1
SELECT *,LEN(Item+'_')-1 'L' from splitstring('a,, c ')
Item | L
--- | ---
a | 1
| 0
c | 3
*/
Antwoord 27
De gemakkelijkste manier:
- SQL Server 2016 installeren
- Gebruik STRING_SPLIT https://msdn.microsoft.com/en-us/ bibliotheek/mt684588.aspx
Het werkt zelfs in de express-editie :).