sql - Comment diviser une chaîne pour pouvoir accéder à l'élément x?

Translate

À l'aide de SQL Server, comment fractionner une chaîne pour pouvoir accéder à l'élément x?

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

This question and all comments follow the "Attribution Required."

Toutes les réponses

Translate

Vous pouvez trouver la solution dansFonction définie par l'utilisateur SQL pour analyser une chaîne délimitéeutile (deLe projet 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
La source
Translate

Je ne pense pas que SQL Server ait une fonction de fractionnement intégrée, donc à part un 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 point. Il prend un nombre comme deuxième argument, et ce nombre spécifie le segment de la chaîne à renvoyer (de l'arrière vers l'avant).

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

Le problème évident est lorsque la chaîne contient déjà un point. Je pense toujours que l'utilisation d'un UDF est la meilleure façon ... d'autres suggestions?

La source
Translate

Tout d'abord, créez une fonction (en utilisant CTE, l'expression de table commune élimine le besoin 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-la pour qu'elle tienne dans votre processus stocké existant) comme ceci.

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

Mettre à jour

La version précédente échouait pour la 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.

La source
Translate

La plupart des solutions ici utilisent des boucles while ou des CTE récursifs. Une approche basée sur les ensembles sera supérieure, je vous 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 de fractionnement, 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 les chaînes de fractionnement provenant de la couche d'application:

Sur SQL Server 2016 ou supérieur, cependant, vous devriez regarderSTRING_SPLIT()etSTRING_AGG():

La source
Translate

Vous pouvez utiliser une table numérique pour effectuer l'analyse de chaîne.

Créez 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 1000000 lignes

    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 (génère 3 mil lignes en 40 secondes sur mon ordinateur portable)

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

nettoyer

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

Les performances ici ne sont pas étonnantes, mais appeler une fonction sur une table d'un million de lignes n'est pas la meilleure idée. Si vous exécutez une chaîne divisée sur plusieurs lignes, j'éviterais la fonction.

La source
Translate

Cette question estpas sur une approche de division de chaîne, mais à propos decomment obtenir le nième élément.

Toutes les réponses ici font une sorte de division de chaîne en utilisant la récursivité,CTEs, multipleCHARINDEX, REVERSEetPATINDEX, inventer des fonctions, appeler des méthodes CLR, des tables de nombres,CROSS APPLYs ... La plupart des réponses couvrent de nombreuses lignes de code.

Mais - si vous vraimentne veulent rien de plus qu'une approche pour obtenir le nième élément- cela peut être fait commevrai one-liner, pas d'UDF, pas même de sous-sélection ... Et comme avantage supplémentaire:type coffre-fort

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ûrvous pouvez utiliser des variablespour délimiteur et position (utilisezsql:columnpour 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 inclureCaractères interdits(en particulier un parmi&><), vous pouvez toujours le faire de cette façon. Juste utiliserFOR XML PATHsur votre chaîne pour remplacer implicitement tous les caractères interdits par 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 je le remplace enfin 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)');

MISE À JOUR pour SQL-Server 2016+

Malheureusement, les développeurs ont oublié de renvoyer l'index de la pièce avecSTRING_SPLIT. Mais, en utilisant SQL-Server 2016+, il y aJSON_VALUEetOPENJSON.

AvecJSON_VALUEnous pouvons passer la position en tant que tableau d'index.

PourOPENJSONlaDocumentationdéclare clairement:

Lorsque OPENJSON analyse un tableau JSON, la fonction renvoie les index des éléments dans le texte JSON sous forme de clés.

Une chaîne comme1,2,3n'a besoin de rien de plus que des crochets:[1,2,3].
Une chaîne de mots commethis is an exampledoit être["this","is","an","example"].
Ce sont des opérations de chaîne très simples. Essayez-le simplement:

DECLARE @str VARCHAR(100)='Hello John Smith';
DECLARE @position INT = 2;

--We can build the json-path '$[1]' using CONCAT
SELECT JSON_VALUE('["' + REPLACE(@str,' ','","') + '"]',CONCAT('$[',@position-1,']'));

- Voir ceci pour un séparateur de chaîne de sécurité de position (base zéro):

SELECT  JsonArray.[key] AS [Position]
       ,JsonArray.[value] AS [Part]
FROM OPENJSON('["' + REPLACE(@str,' ','","') + '"]') JsonArray

Dansce postJ'ai testé différentes approches et constaté queOPENJSONest vraiment rapide. Encore beaucoup plus rapide que la fameuse méthode "delimitedSplit8k ()" ...

La source
Translate

Voici un UDF qui le fera. Il renverra un tableau des valeurs délimitées, je n'ai pas essayé tous les scénarios dessus 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'appeleriez 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','**')
La source
Translate

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',' ')
La source
Translate

À mon avis, vous rendez les choses beaucoup trop compliquées. Créez simplement un CLR UDF et en avez terminé avec lui.

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()));
  }
};
La source
Translate

Qu'en est-il de l'utilisationstringetvalues()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

Ensemble de résultats atteint.

id  item
1   Hello
2   John
3   Smith
La source
Translate

J'utilise la réponse de frederic mais cela n'a pas fonctionné dans SQL Server 2005

Je l'ai modifié et j'utiliseselectavecunion allet il fonctionne

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

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

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

SET @str = REPLACE(@str, @separator, ''' UNION ALL SELECT ''')
SET @str = ' SELECT  ''' + @str + '''  ' 

INSERT INTO @Splited
EXEC(@str)

SELECT * FROM @Splited

Et l'ensemble de résultats est:

id  item
1   Hello
2   John
3   Smith
4   how
5   are
6   you
La source
Translate

Ce modèle fonctionne très bien et vous pouvez généraliser

Convert(xml,'<n>'+Replace(FIELD,'.','</n><n>')+'</n>').value('(/n[INDEX])','TYPE')
                          ^^^^^                                   ^^^^^     ^^^^

RemarqueCHAMP, INDICEetTYPE.

Laissez une table avec des identifiants comme

sys.message.1234.warning.A45
sys.message.1235.error.O98
....

Ensuite, vous pouvez écrire

SELECT Source         = q.value('(/n[1])', 'varchar(10)'),
       RecordType     = q.value('(/n[2])', 'varchar(20)'),
       RecordNumber   = q.value('(/n[3])', 'int'),
       Status         = q.value('(/n[4])', 'varchar(5)')
FROM   (
         SELECT   q = Convert(xml,'<n>'+Replace(fieldName,'.','</n><n>')+'</n>')
         FROM     some_TABLE
       ) Q

fendre et couler toutes les pièces.

La source
Translate

Si votre base de données a un niveau de compatibilité de 130 ou plus, vous pouvez utiliser leSTRING_SPLITfonctionner avecFETCH OFFSETclauses pour obtenir l'élément spécifique par index.

Pour obtenir l'article àindex N(basé sur zéro), vous pouvez utiliser le code suivant

SELECT value
FROM STRING_SPLIT('Hello John Smith',' ')
ORDER BY (SELECT NULL)
OFFSET N ROWS
FETCH NEXT 1 ROWS ONLY

Pour vérifier leniveau de compatibilité de votre base de données, exécutez ce code:

SELECT compatibility_level  
FROM sys.databases WHERE name = 'YourDBName';
La source
kta
Translate

Je cherchais la solution sur le net et ce qui suit fonctionne pour moi.Réf.

Et vous appelez la fonction comme ceci:

SELECT * FROM dbo.split('ram shyam hari gopal',' ')

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[Split](@String VARCHAR(8000), @Delimiter CHAR(1))       
RETURNS @temptable TABLE (items VARCHAR(8000))       
AS       
BEGIN       
    DECLARE @idx INT       
    DECLARE @slice VARCHAR(8000)        
    SELECT @idx = 1       
    IF len(@String)<1 OR @String IS NULL  RETURN       
    WHILE @idx!= 0       
    BEGIN       
        SET @idx = charindex(@Delimiter,@String)       
        IF @idx!=0       
            SET @slice = LEFT(@String,@idx - 1)       
        ELSE       
            SET @slice = @String       
        IF(len(@slice)>0)  
            INSERT INTO @temptable(Items) VALUES(@slice)       
        SET @String = RIGHT(@String,len(@String) - @idx)       
        IF len(@String) = 0 break       
    END   
    RETURN       
END
La source
Translate

Encore une autre partie de la chaîne par la fonction de délimitation:

create function GetStringPartByDelimeter (
    @value as nvarchar(max),
    @delimeter as nvarchar(max),
    @position as int
) returns NVARCHAR(MAX) 
AS BEGIN
    declare @startPos as int
    declare @endPos as int
    set @endPos = -1
    while (@position > 0 and @endPos != 0) begin
        set @startPos = @endPos + 1
        set @endPos = charindex(@delimeter, @value, @startPos)

        if(@position = 1) begin
            if(@endPos = 0)
                set @endPos = len(@value) + 1

            return substring(@value, @startPos, @endPos - @startPos)
        end

        set @position = @position - 1
    end

    return null
end

et l'utilisation:

select dbo.GetStringPartByDelimeter ('a;b;c;d;e', ';', 3)

qui renvoie:

c
La source
Translate

Essaye ça:

CREATE function [SplitWordList]
(
 @list varchar(8000)
)
returns @t table 
(
 Word varchar(50) not null,
 Position int identity(1,1) not null
)
as begin
  declare 
    @pos int,
    @lpos int,
    @item varchar(100),
    @ignore varchar(100),
    @dl int,
    @a1 int,
    @a2 int,
    @z1 int,
    @z2 int,
    @n1 int,
    @n2 int,
    @c varchar(1),
    @a smallint
  select 
    @a1 = ascii('a'),
    @a2 = ascii('A'),
    @z1 = ascii('z'),
    @z2 = ascii('Z'),
    @n1 = ascii('0'),
    @n2 = ascii('9')
  set @ignore = '''"'
  set @pos = 1
  set @dl = datalength(@list)
  set @lpos = 1
  set @item = ''
  while (@pos <= @dl) begin
    set @c = substring(@list, @pos, 1)
    if (@ignore not like '%' + @c + '%') begin
      set @a = ascii(@c)
      if ((@a >= @a1) and (@a <= @z1))  
        or ((@a >= @a2) and (@a <= @z2))
        or ((@a >= @n1) and (@a <= @n2))
      begin
        set @item = @item + @c
      end else if (@item > '') begin
        insert into @t values (@item)
        set @item = ''
      end
    end 
    set @pos = @pos + 1
  end
  if (@item > '') begin
    insert into @t values (@item)
  end
  return
end

Testez-le comme ceci:

select * from SplitWordList('Hello John Smith')
La source
Translate

L'exemple suivant utilise un CTE récursif

Mettre à jour18.09.2013

CREATE FUNCTION dbo.SplitStrings_CTE(@List nvarchar(max), @Delimiter nvarchar(1))
RETURNS @returns TABLE (val nvarchar(max), [level] int, PRIMARY KEY CLUSTERED([level]))
AS
BEGIN
;WITH cte AS
 (
  SELECT SUBSTRING(@List, 0, CHARINDEX(@Delimiter,  @List + @Delimiter)) AS val,
         CAST(STUFF(@List + @Delimiter, 1, CHARINDEX(@Delimiter, @List + @Delimiter), '') AS nvarchar(max)) AS stval, 
         1 AS [level]
  UNION ALL
  SELECT SUBSTRING(stval, 0, CHARINDEX(@Delimiter, stval)),
         CAST(STUFF(stval, 1, CHARINDEX(@Delimiter, stval), '') AS nvarchar(max)),
         [level] + 1
  FROM cte
  WHERE stval != ''
  )
  INSERT @returns
  SELECT REPLACE(val, ' ','' ) AS val, [level]
  FROM cte
  WHERE val > ''
  RETURN
END

Démo surSQLFiddle

La source
Translate


    Alter Function dbo.fn_Split
    (
    @Expression nvarchar(max),
    @Delimiter  nvarchar(20) = ',',
    @Qualifier  char(1) = Null
    )
    RETURNS @Results TABLE (id int IDENTITY(1,1), value nvarchar(max))
    AS
    BEGIN
       /* USAGE
            Select * From dbo.fn_Split('apple pear grape banana orange honeydew cantalope 3 2 1 4', ' ', Null)
            Select * From dbo.fn_Split('1,abc,"Doe, John",4', ',', '"')
            Select * From dbo.fn_Split('Hello 0,"&""&&&&', ',', '"')
       */

       -- Declare Variables
       DECLARE
          @X     xml,
          @Temp  nvarchar(max),
          @Temp2 nvarchar(max),
          @Start int,
          @End   int

       -- HTML Encode @Expression
       Select @Expression = (Select @Expression For XML Path(''))

       -- Find all occurences of @Delimiter within @Qualifier and replace with |||***|||
       While PATINDEX('%' + @Qualifier + '%', @Expression) > 0 AND Len(IsNull(@Qualifier, '')) > 0
       BEGIN
          Select
             -- Starting character position of @Qualifier
             @Start = PATINDEX('%' + @Qualifier + '%', @Expression),
             -- @Expression starting at the @Start position
             @Temp = SubString(@Expression, @Start + 1, LEN(@Expression)[email protected]+1),
             -- Next position of @Qualifier within @Expression
             @End = PATINDEX('%' + @Qualifier + '%', @Temp) - 1,
             -- The part of Expression found between the @Qualifiers
             @Temp2 = Case When @End < 0 Then @Temp Else Left(@Temp, @End) End,
             -- New @Expression
             @Expression = REPLACE(@Expression,
                                   @Qualifier + @Temp2 + Case When @End < 0 Then '' Else @Qualifier End,
                                   Replace(@Temp2, @Delimiter, '|||***|||')
                           )
       END

       -- Replace all occurences of @Delimiter within @Expression with '</fn_Split>&ltfn_Split>'
       -- And convert it to XML so we can select from it
       SET
          @X = Cast('&ltfn_Split>' +
                    Replace(@Expression, @Delimiter, '</fn_Split>&ltfn_Split>') +
                    '</fn_Split>' as xml)

       -- Insert into our returnable table replacing '|||***|||' back to @Delimiter
       INSERT @Results
       SELECT
          "Value" = LTRIM(RTrim(Replace(C.value('.', 'nvarchar(max)'), '|||***|||', @Delimiter)))
       FROM
          @X.nodes('fn_Split') as X(C)

       -- Return our temp table
       RETURN
    END

La source
Translate

Je sais que c'est une vieille question, mais je pense que quelqu'un peut bénéficier de ma solution.

select 
SUBSTRING(column_name,1,CHARINDEX(' ',column_name,1)-1)
,SUBSTRING(SUBSTRING(column_name,CHARINDEX(' ',column_name,1)+1,LEN(column_name))
    ,1
    ,CHARINDEX(' ',SUBSTRING(column_name,CHARINDEX(' ',column_name,1)+1,LEN(column_name)),1)-1)
,SUBSTRING(SUBSTRING(column_name,CHARINDEX(' ',column_name,1)+1,LEN(column_name))
    ,CHARINDEX(' ',SUBSTRING(column_name,CHARINDEX(' ',column_name,1)+1,LEN(column_name)),1)+1
    ,LEN(column_name))
from table_name

SQL FIDDLE

Avantages:

  • Il sépare tous les 3 déliminateurs de sous-chaînes par «».
  • Il ne faut pas utiliser la boucle while, car cela diminue les performances.
  • Pas besoin de pivoter car toute la sous-chaîne résultante sera affichée dans une ligne

Limites:

  • Il faut connaître le non total. d'espaces (sous-chaîne).

Remarque: la solution peut donner une sous-chaîne jusqu'à N.

Pour surmonter la limitation, nous pouvons utiliser ce qui suitréf.

Mais encore une fois ce qui précèdeSolutionne peut pas être utilisé dans une table (Actaully je n'ai pas pu l'utiliser).

Encore une fois, j'espère que cette solution peut aider quelqu'un.

Mettre à jour:En cas d'enregistrements> 50000, ce n'est pasconseilléutiliserLOOPScar cela dégradera lePerformance

La source
Luv
Translate

Presque tous les autres codes de division des réponses remplacent la chaîne en cours de division, ce qui gaspille des cycles de processeur et effectue des allocations de mémoire inutiles.

Je couvre une bien meilleure façon de faire une division de cordes ici:http://www.digitalruby.com/split-string-sql-server/

Voici le code:

SET NOCOUNT ON

-- You will want to change nvarchar(MAX) to nvarchar(50), varchar(50) or whatever matches exactly with the string column you will be searching against
DECLARE @SplitStringTable TABLE (Value nvarchar(MAX) NOT NULL)
DECLARE @StringToSplit nvarchar(MAX) = 'your|string|to|split|here'
DECLARE @SplitEndPos int
DECLARE @SplitValue nvarchar(MAX)
DECLARE @SplitDelim nvarchar(1) = '|'
DECLARE @SplitStartPos int = 1

SET @SplitEndPos = CHARINDEX(@SplitDelim, @StringToSplit, @SplitStartPos)

WHILE @SplitEndPos > 0
BEGIN
    SET @SplitValue = SUBSTRING(@StringToSplit, @SplitStartPos, (@SplitEndPos - @SplitStartPos))
    INSERT @SplitStringTable (Value) VALUES (@SplitValue)
    SET @SplitStartPos = @SplitEndPos + 1
    SET @SplitEndPos = CHARINDEX(@SplitDelim, @StringToSplit, @SplitStartPos)
END

SET @SplitValue = SUBSTRING(@StringToSplit, @SplitStartPos, 2147483647)
INSERT @SplitStringTable (Value) VALUES(@SplitValue)

SET NOCOUNT OFF

-- You can select or join with the values in @SplitStringTable at this point.
La source
Translate

Vous pouvez diviser une chaîne en SQL sans avoir besoin d'une fonction:

DECLARE @bla varchar(MAX)
SET @bla = 'BED40DFC-F468-46DD-8017-00EF2FA3E4A4,64B59FC5-3F4D-4B0E-9A48-01F3D4F220B0,A611A108-97CA-42F3-A2E1-057165339719,E72D95EA-578F-45FC-88E5-075F66FD726C'

-- http://stackoverflow.com/questions/14712864/how-to-query-values-from-xml-nodes
SELECT 
    x.XmlCol.value('.', 'varchar(36)') AS val 
FROM 
(
    SELECT 
    CAST('<e>' + REPLACE(@bla, ',', '</e><e>') + '</e>' AS xml) AS RawXml
) AS b 
CROSS APPLY b.RawXml.nodes('e') x(XmlCol);

Si vous avez besoin de prendre en charge des chaînes arbitraires (avec des caractères spéciaux xml)

DECLARE @bla NVARCHAR(MAX)
SET @bla = '<html>unsafe & safe Utf8CharsDon''tGetEncoded ÄöÜ - "Conex"<html>,Barnes & Noble,abc,def,ghi'

-- http://stackoverflow.com/questions/14712864/how-to-query-values-from-xml-nodes
SELECT 
    x.XmlCol.value('.', 'nvarchar(MAX)') AS val 
FROM 
(
    SELECT 
    CAST('<e>' + REPLACE((SELECT @bla FOR XML PATH('')), ',', '</e><e>') + '</e>' AS xml) AS RawXml
) AS b 
CROSS APPLY b.RawXml.nodes('e') x(XmlCol); 
La source
Translate

Solution pure basée sur des ensembles utilisantTVFavec récursifCTE. Vous pouvezJOINetAPPLYcette fonction à n'importe quel ensemble de données.

create function [dbo].[SplitStringToResultSet] (@value varchar(max), @separator char(1))
returns table
as return
with r as (
    select value, cast(null as varchar(max)) [x], -1 [no] from (select rtrim(cast(@value as varchar(max))) [value]) as j
    union all
    select right(value, len(value)-case charindex(@separator, value) when 0 then len(value) else charindex(@separator, value) end) [value]
    , left(r.[value], case charindex(@separator, r.value) when 0 then len(r.value) else abs(charindex(@separator, r.[value])-1) end ) [x]
    , [no] + 1 [no]
    from r where value > '')

select ltrim(x) [value], [no] [index] from r where x is not null;
go

Usage:

select *
from [dbo].[SplitStringToResultSet]('Hello John Smith', ' ')
where [index] = 1;

Résultat:

value   index
-------------
John    1
La source
Translate

Commençant parSQL Server 2016nousstring_split

DECLARE @string varchar(100) = 'Richard, Mike, Mark'

SELECT value FROM string_split(@string, ',')
La source
Translate

Une approche moderne utilisantSTRING_SPLIT, nécessite SQL Server 2016 et supérieur.

DECLARE @string varchar(100) = 'Hello John Smith'

SELECT
    ROW_NUMBER() OVER (ORDER BY value) AS RowNr,
    value
FROM string_split(@string, ' ')

Résultat:

RowNr   value
1       Hello
2       John
3       Smith

Il est maintenant possible d'obtenir le nième élément à partir du numéro de ligne.

La source
Translate

La réponse d'Aaron Bertrand est excellente, mais imparfaite. Il ne gère pas avec précision un espace comme délimiteur (comme c'était le cas dans la question d'origine) car la fonction length supprime les espaces de fin.

Ce qui suit est son code, avec un petit ajustement pour permettre un délimiteur d'espace:

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+'x')-1) = @Delim
      ) AS y
    );
La source
Translate

Voici une fonction qui accomplira l'objectif de la question de fractionner une chaîne et d'accéder à l'élément X:

CREATE FUNCTION [dbo].[SplitString]
(
   @List       VARCHAR(MAX),
   @Delimiter  VARCHAR(255),
   @ElementNumber INT
)
RETURNS VARCHAR(MAX)
AS
BEGIN

       DECLARE @inp VARCHAR(MAX)
       SET @inp = (SELECT REPLACE(@List,@Delimiter,'_DELMTR_') FOR XML PATH(''))

       DECLARE @xml XML
       SET @xml = '<split><el>' + REPLACE(@inp,'_DELMTR_','</el><el>') + '</el></split>'

       DECLARE @ret VARCHAR(MAX)
       SET @ret = (SELECT
              el = split.el.value('.','varchar(max)')
       FROM  @xml.nodes('/split/el[string-length(.)>0][position() = sql:variable("@elementnumber")]') split(el))

       RETURN @ret

END

Usage:

SELECT dbo.SplitString('Hello John Smith', ' ', 2)

Résultat:

John
La source
Translate

SOLUTION SIMPLE POUR LA SÉLECTION DU PREMIER ET DU DERNIER NOM

DECLARE @Name varchar(10) = 'John Smith'

-- Get First Name
SELECT SUBSTRING(@Name, 0, (SELECT CHARINDEX(' ', @Name)))

-- Get Last Name
SELECT SUBSTRING(@Name, (SELECT CHARINDEX(' ', @Name)) + 1, LEN(@Name))

Dans mon cas (et dans beaucoup d'autres il semble ...), j'ai une liste de prénoms et de noms séparés par un seul espace. Cela peut être utilisé directement dans une instruction select pour analyser le prénom et le nom.

-- i.e. Get First and Last Name from a table of Full Names
SELECT SUBSTRING(FullName, 0, (SELECT CHARINDEX(' ', FullName))) as FirstName,
SUBSTRING(FullName, (SELECT CHARINDEX(' ', FullName)) + 1, LEN(FullName)) as LastName,
From FullNameTable
La source
Translate

Je sais que c'est tard, mais j'ai récemment eu cette exigence et j'ai trouvé le code ci-dessous. Je n'ai pas le choix d'utiliser la fonction définie par l'utilisateur. J'espère que cela t'aides.

SELECT 
    SUBSTRING(
                SUBSTRING('Hello John Smith' ,0,CHARINDEX(' ','Hello John Smith',CHARINDEX(' ','Hello John Smith')+1)
                        ),CHARINDEX(' ','Hello John Smith'),LEN('Hello John Smith')
            )
La source
Translate

Eh bien, le mien n'est pas si simple que cela, mais voici le code que j'utilise pour diviser une variable d'entrée délimitée par des virgules en valeurs individuelles et la mettre dans une variable de table. Je suis sûr que vous pouvez modifier légèrement ceci pour diviser en fonction d'un espace, puis effectuer une requête SELECT de base sur cette variable de table pour obtenir vos résultats.

-- Create temporary table to parse the list of accounting cycles.
DECLARE @tblAccountingCycles table
(
    AccountingCycle varchar(10)
)

DECLARE @vchAccountingCycle varchar(10)
DECLARE @intPosition int

SET @vchAccountingCycleIDs = LTRIM(RTRIM(@vchAccountingCycleIDs)) + ','
SET @intPosition = CHARINDEX(',', @vchAccountingCycleIDs, 1)

IF REPLACE(@vchAccountingCycleIDs, ',', '') <> ''
BEGIN
    WHILE @intPosition > 0
    BEGIN
        SET @vchAccountingCycle = LTRIM(RTRIM(LEFT(@vchAccountingCycleIDs, @intPosition - 1)))
        IF @vchAccountingCycle <> ''
        BEGIN
            INSERT INTO @tblAccountingCycles (AccountingCycle) VALUES (@vchAccountingCycle)
        END
        SET @vchAccountingCycleIDs = RIGHT(@vchAccountingCycleIDs, LEN(@vchAccountingCycleIDs) - @intPosition)
        SET @intPosition = CHARINDEX(',', @vchAccountingCycleIDs, 1)
    END
END

Le concept est à peu près le même. Une autre alternative consiste à tirer parti de la compatibilité .NET dans SQL Server 2005 lui-même. Vous pouvez essentiellement vous écrire une méthode simple dans .NET qui diviserait la chaîne, puis l'exposerait en tant que procédure / fonction stockée.

La source
Translate

C'est quelque chose que j'ai fait pour obtenir un jeton spécifique dans une chaîne. (Testé dans MSSQL 2008)

Tout d'abord, créez les fonctions suivantes: (trouvées dans:ici

CREATE FUNCTION dbo.SplitStrings_Moden
(
   @List NVARCHAR(MAX),
   @Delimiter NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING AS
RETURN
  WITH E1(N)        AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 
                         UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 
                         UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),
       E2(N)        AS (SELECT 1 FROM E1 a, E1 b),
       E4(N)        AS (SELECT 1 FROM E2 a, E2 b),
       E42(N)       AS (SELECT 1 FROM E4 a, E2 b),
       cteTally(N)  AS (SELECT 0 UNION ALL SELECT TOP (DATALENGTH(ISNULL(@List,1))) 
                         ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E42),
       cteStart(N1) AS (SELECT t.N+1 FROM cteTally t
                         WHERE (SUBSTRING(@List,t.N,1) = @Delimiter OR t.N = 0))
  SELECT Item = SUBSTRING(@List, s.N1, ISNULL(NULLIF(CHARINDEX(@Delimiter,@List,s.N1),0)-s.N1,8000))
    FROM cteStart s;

et

create FUNCTION dbo.getToken
(
@List NVARCHAR(MAX),
@Delimiter NVARCHAR(255),
@Pos int
)
RETURNS varchar(max)
as 
begin
declare @returnValue varchar(max);
select @returnValue = tbl.Item from (
select ROW_NUMBER() over (order by (select null)) as id, * from dbo.SplitStrings_Moden(@List, @Delimiter)
) as tbl
where tbl.id = @Pos
return @returnValue
end

alors vous pouvez l'utiliser comme ça:

select dbo.getToken('1111_2222_3333_', '_', 1)

qui retournent 1111

La source