Question Comment séparer une chaîne pour pouvoir accéder à l'élément x?


En utilisant SQL Server, comment diviser une chaîne pour pouvoir accéder à l'élément x?

Prenez une chaîne "Bonjour John Smith". Comment diviser la chaîne par espace et accéder à l'élément à l'index 1 qui doit renvoyer "John"?


451
2017-08-05 18:15


origine


Réponses:


Vous pouvez trouver la solution dans Fonction définie par l'utilisateur SQL pour analyser une chaîne délimitée utile (de Le projet de code).

Vous pouvez utiliser cette logique simple:

Declare @products varchar(200) = '1|20|3|343|44|6|8765'
Declare @individual varchar(20) = null

WHILE LEN(@products) > 0
BEGIN
    IF PATINDEX('%|%', @products) > 0
    BEGIN
        SET @individual = SUBSTRING(@products,
                                    0,
                                    PATINDEX('%|%', @products))
        SELECT @individual

        SET @products = SUBSTRING(@products,
                                  LEN(@individual + '|') + 1,
                                  LEN(@products))
    END
    ELSE
    BEGIN
        SET @individual = @products
        SET @products = NULL
        SELECT @individual
    END
END

179
2017-08-05 18:28



Je ne crois pas que SQL Server dispose d'une fonction de division intégrée, à part une fonction UDF, la seule autre réponse que je connaisse est de détourner la fonction PARSENAME:

SELECT PARSENAME(REPLACE('Hello John Smith', ' ', '.'), 2) 

PARSENAME prend une chaîne et la divise sur le caractère de période. Il prend un nombre comme deuxième argument, et ce nombre spécifie quel segment de la chaîne retourner (travailler de l'arrière vers l'avant).

SELECT PARSENAME(REPLACE('Hello John Smith', ' ', '.'), 3)  --return Hello

Le problème évident est quand la chaîne contient déjà une période. Je pense toujours que l'utilisation d'un UDF est le meilleur moyen ... d'autres suggestions?


337
2017-08-05 18:45



Tout d'abord, créez une fonction (en utilisant CTE, l'expression de table commune supprime la nécessité d'une table temporaire)

 create function dbo.SplitString 
    (
        @str nvarchar(4000), 
        @separator char(1)
    )
    returns table
    AS
    return (
        with tokens(p, a, b) AS (
            select 
                1, 
                1, 
                charindex(@separator, @str)
            union all
            select
                p + 1, 
                b + 1, 
                charindex(@separator, @str, b + 1)
            from tokens
            where b > 0
        )
        select
            p-1 zeroBasedOccurance,
            substring(
                @str, 
                a, 
                case when b > 0 then b-a ELSE 4000 end) 
            AS s
        from tokens
      )
    GO

Ensuite, utilisez-le comme n'importe quelle table (ou modifiez-le pour l'adapter à votre procédure stockée existante) comme ceci.

select s 
from dbo.SplitString('Hello John Smith', ' ')
where zeroBasedOccurance=1

Mettre à jour

La version précédente échouerait pour une chaîne d'entrée de plus de 4000 caractères. Cette version prend en charge la limitation:

create function dbo.SplitString 
(
    @str nvarchar(max), 
    @separator char(1)
)
returns table
AS
return (
with tokens(p, a, b) AS (
    select 
        cast(1 as bigint), 
        cast(1 as bigint), 
        charindex(@separator, @str)
    union all
    select
        p + 1, 
        b + 1, 
        charindex(@separator, @str, b + 1)
    from tokens
    where b > 0
)
select
    p-1 ItemIndex,
    substring(
        @str, 
        a, 
        case when b > 0 then b-a ELSE LEN(@str) end) 
    AS s
from tokens
);

GO

L'utilisation reste la même.


106
2017-08-05 18:57



La plupart des solutions utilisées ici utilisent des boucles while ou des CTE récursives. Une approche basée sur un ensemble sera supérieure, je le promets:

CREATE FUNCTION [dbo].[SplitString]
    (
        @List NVARCHAR(MAX),
        @Delim VARCHAR(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_objects) AS x
              WHERE Number <= LEN(@List)
              AND SUBSTRING(@Delim + @List, [Number], LEN(@Delim)) = @Delim
          ) AS y
        );

Plus d'informations sur les fonctions split, pourquoi (et la preuve que) alors que les boucles et les CTE récursifs ne sont pas mis à l'échelle, et de meilleures alternatives, si vous séparez des chaînes provenant de la couche application:

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

http://sqlblog.com/blogs/aaron_bertrand/archive/2010/07/07/splitting-a-list-of-integers-another-roundup.aspx


53
2017-11-12 17:16



Vous pouvez utiliser une table Number pour effectuer l'analyse de chaîne.

Créer une table de nombres physiques:

    create table dbo.Numbers (N int primary key);
    insert into dbo.Numbers
        select top 1000 row_number() over(order by number) from master..spt_values
    go

Créer une table de test avec des rangées de 1000000

    create table #yak (i int identity(1,1) primary key, array varchar(50))

    insert into #yak(array)
        select 'a,b,c' from dbo.Numbers n cross join dbo.Numbers nn
    go

Créer la fonction

    create function [dbo].[ufn_ParseArray]
        (   @Input      nvarchar(4000), 
            @Delimiter  char(1) = ',',
            @BaseIdent  int
        )
    returns table as
    return  
        (   select  row_number() over (order by n asc) + (@BaseIdent - 1) [i],
                    substring(@Input, n, charindex(@Delimiter, @Input + @Delimiter, n) - n) s
            from    dbo.Numbers
            where   n <= convert(int, len(@Input)) and
                    substring(@Delimiter + @Input, n, 1) = @Delimiter
        )
    go

Utilisation (sorties 3mil rangs dans 40s sur mon portable)

    select * 
    from #yak 
    cross apply dbo.ufn_ParseArray(array, ',', 1)

nettoyer

    drop table dbo.Numbers;
    drop function  [dbo].[ufn_ParseArray]

La performance ici n'est pas étonnante, mais appeler une fonction sur une table de millions de lignes n'est pas la meilleure idée. Si vous effectuez une chaîne divisée sur plusieurs lignes, j'éviterai la fonction.


37
2017-10-27 16:48



Voici un UDF qui le fera. Il retournera une table des valeurs délimitées, n’a pas essayé tous les scénarios mais votre exemple fonctionne bien.


CREATE FUNCTION SplitString 
(
    -- Add the parameters for the function here
    @myString varchar(500),
    @deliminator varchar(10)
)
RETURNS 
@ReturnTable TABLE 
(
    -- Add the column definitions for the TABLE variable here
    [id] [int] IDENTITY(1,1) NOT NULL,
    [part] [varchar](50) NULL
)
AS
BEGIN
        Declare @iSpaces int
        Declare @part varchar(50)

        --initialize spaces
        Select @iSpaces = charindex(@deliminator,@myString,0)
        While @iSpaces > 0

        Begin
            Select @part = substring(@myString,0,charindex(@deliminator,@myString,0))

            Insert Into @ReturnTable(part)
            Select @part

    Select @myString = substring(@mystring,charindex(@deliminator,@myString,0)+ len(@deliminator),len(@myString) - charindex(' ',@myString,0))


            Select @iSpaces = charindex(@deliminator,@myString,0)
        end

        If len(@myString) > 0
            Insert Into @ReturnTable
            Select @myString

    RETURN 
END
GO

Vous l'appelleriez comme ceci:


Select * From SplitString('Hello John Smith',' ')

Edit: Solution mise à jour pour gérer les délimiteurs avec un len> 1 comme dans:


select * From SplitString('Hello**John**Smith','**')

20
2017-08-05 18:39



Pas de code, mais lisez l'article définitif à ce sujet. Tout les solutions dans d'autres réponses sont des saveurs de celles énumérées dans cet article: Tableaux et listes dans SQL Server 2005 et au-delà

Personnellement, j'ai utilisé le plus souvent une solution de table Numbers car elle convient à ce que je dois faire ...


16
2017-09-26 13:44



Ici, je poste un moyen simple de solution

CREATE FUNCTION [dbo].[split](
          @delimited NVARCHAR(MAX),
          @delimiter NVARCHAR(100)
        ) RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX))
        AS
        BEGIN
          DECLARE @xml XML
          SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>'

          INSERT INTO @t(val)
          SELECT  r.value('.','varchar(MAX)') as item
          FROM  @xml.nodes('/t') as records(r)
          RETURN
        END


    Exécutez la fonction comme ceci

  select * from dbo.split('Hello John Smith',' ')

15
2018-01-30 09:41



Cette question est pas sur une approche de partage de chaîne, mais à propos de comment obtenir le nième élément.

Toutes les réponses ici font une sorte de fente de chaîne en utilisant la récursivité, CTEs, multiple CHARINDEX, REVERSE et PATINDEX, inventer des fonctions, appeler des méthodes CLR, des tables numériques, CROSS APPLYs ... La plupart des réponses couvrent plusieurs lignes de code.

Mais - si vous vraiment ne veulent rien de plus qu'une approche pour obtenir le nième élément - Cela peut être fait comme vrai one-liner, pas de UDF, pas même une sous-sélection ... Et comme un avantage supplémentaire: type sûr

Obtenez la partie 2 délimitée par un espace:

DECLARE @input NVARCHAR(100)=N'part1 part2 part3';
SELECT CAST(N'<x>' + REPLACE(@input,N' ',N'</x><x>') + N'</x>' AS XML).value('/x[2]','nvarchar(max)')

Bien sûr vous pouvez utiliser des variables pour délimiteur et position (utilisation sql:column récupérer la position directement à partir de la valeur d'une requête):

DECLARE @dlmt NVARCHAR(10)=N' ';
DECLARE @pos INT = 2;
SELECT CAST(N'<x>' + REPLACE(@input,@dlmt,N'</x><x>') + N'</x>' AS XML).value('/x[sql:variable("@pos")][1]','nvarchar(max)')

Si votre chaîne peut inclure Caractères interdits (surtout parmi &><), vous pouvez toujours le faire de cette façon. Juste utiliser FOR XML PATH sur votre chaîne en premier pour remplacer implicitement tous les caractères interdits avec la séquence d'échappement appropriée.

C'est un cas très spécial si - en plus - votre délimiteur est le point-virgule. Dans ce cas, je remplace d'abord le délimiteur par '# DLMT #', et le remplace finalement par les balises XML:

SET @input=N'Some <, > and &;Other äöü@€;One more';
SET @dlmt=N';';
SELECT CAST(N'<x>' + REPLACE((SELECT REPLACE(@input,@dlmt,'#DLMT#') AS [*] FOR XML PATH('')),N'#DLMT#',N'</x><x>') + N'</x>' AS XML).value('/x[sql:variable("@pos")][1]','nvarchar(max)');

13
2017-07-08 20:41



À mon avis, vous êtes trop compliqué. Il suffit de créer un fichier UDF CLR et de le terminer.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections.Generic;

public partial class UserDefinedFunctions {
  [SqlFunction]
  public static SqlString SearchString(string Search) {
    List<string> SearchWords = new List<string>();
    foreach (string s in Search.Split(new char[] { ' ' })) {
      if (!s.ToLower().Equals("or") && !s.ToLower().Equals("and")) {
        SearchWords.Add(s);
      }
    }

    return new SqlString(string.Join(" OR ", SearchWords.ToArray()));
  }
};

10
2017-07-19 21:46



Pourquoi ne pas utiliser string et values() déclaration?

DECLARE @str varchar(max)
SET @str = 'Hello John Smith'

DECLARE @separator varchar(max)
SET @separator = ' '

DECLARE @Splited TABLE(id int IDENTITY(1,1), item varchar(max))

SET @str = REPLACE(@str, @separator, '''),(''')
SET @str = 'SELECT * FROM (VALUES(''' + @str + ''')) AS V(A)' 

INSERT INTO @Splited
EXEC(@str)

SELECT * FROM @Splited

Résultat-ensemble atteint.

id  item
1   Hello
2   John
3   Smith

10
2018-03-01 16:26