T-SQL gesplitste tekenreeks

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)[email protected])
 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:


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)[email protected])
 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)[email protected])
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 @[email protected]+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)[email protected])
 END
    SELECT @[email protected]+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)[email protected])
 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 = @[email protected]
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 = [email protected],
                   @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)[email protected])
    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)[email protected])       
    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, '&', '&amp;'), '<', '&lt;'), '>', '&gt;'), '''', '&apos;'), '"', '&quot;');
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:

  1. SQL Server 2016 installeren
  2. Gebruik STRING_SPLIT https://msdn.microsoft.com/en-us/ bibliotheek/mt684588.aspx

Het werkt zelfs in de express-editie :).

Other episodes