Comment diviser une chaîne délimitée dans SQL Server sans créer de fonction?

une version utilisant XML.

declare @S varchar(100) = 'Hello John Smith'

select 
  n.r.value('.', 'varchar(50)')
from (select cast('<r>'+replace(@S, ' ', '</r><r>')+'</r>' as xml)) as s(XMLCol)
  cross apply s.XMLCol.nodes('r') as n(r)

utiliser une table à la place Remplacez @T avec la table que vous utilisez.

-- Test table
declare @T table (ID int, Col varchar(100))
insert into @T values (1, 'Hello John Smith')
insert into @T values (2, 'xxx yyy zzz')

select 
  T.ID,
  n.r.value('.', 'varchar(50)')
from @T as T
  cross apply (select cast('<r>'+replace(replace(Col,'&','&amp;'), ' ', '</r><r>')+'</r>' as xml)) as S(XMLCol)
  cross apply S.XMLCol.nodes('r') as n(r)

fending the string 'Hello John Smith' sans utiliser une variable

select 
  n.r.value('.', 'varchar(50)')
from (select cast('<r>'+replace('Hello John Smith', ' ', '</r><r>')+'</r>' as xml)) as s(XMLCol)
  cross apply s.XMLCol.nodes('r') as n(r)

exemple utilisant le maître intégré..spt_values table

DECLARE @String VARCHAR(1000)
    SELECT @String ='1,4,77,88,4546,234,2,3,54,87,9,6,4,36,6,9,9,6,4,4,68,9,0,5'

    SELECT SUBSTRING(',' + @String + ',', Number + 1,
    CHARINDEX(',', ',' + @String + ',', Number + 1) - Number -1)AS VALUE
    FROM master..spt_values
    WHERE Type = 'P'
    AND Number <= LEN(',' + @String + ',') - 1
    AND SUBSTRING(',' + @String + ',', Number, 1) = ','
    GO

Voir ici pour plus d': Scinder Une Chaîne À L'Aide D'Une Table De Nombre

vous pouvez utiliser le CTE récursif pour extraire progressivement un élément

Exemple de tableau

create table aTable(a int identity primary key, b int, c varchar(100))
insert aTable values (1, 'this is a test string')
insert aTable values (1, 'this is another test string')
insert aTable values (2, 'here is a test string to put the others to shame')
insert aTable values (4, '')
insert aTable values (5, null)
insert aTable values (5, '-the end- ')

La requête

;with tmp(a, b, c, position, single) as (
select a, b,
    STUFF(c, 1, CHARINDEX(' ', c + ' .'), ''),
    1,
    convert(nvarchar(max),left(c, CHARINDEX(' ', c + ' .') -1))
from aTable
union all
select a, b,
    STUFF(c, 1, CHARINDEX(' ', c + ' .'), ''),
    position+1,
    convert(nvarchar(max),left(c, CHARINDEX(' ', c + ' .') -1))
from tmp
where c > '')
select a, b, single, position
from tmp
order by a, position

Notes:

• le délimiteur ici est un espace unique, ce qui est recherché par CHARINDEX. Le point ' .' est nécessaire parce que SQL Server ne voit normalement pas les espaces de fuite comme significatifs.
• toutes les colonnes du tableau original peuvent être conservées dans le CTE, il suffit de les ajouter. Ici je montre un exemple de 2 colonnes a et b conservées dans la sortie, avec la colonne c divisé en single et une colonne supplémentaire pour indiquer la position.

Pour les retardataires à cette question, l'article sur http://www.sqlperformance.com/2012/07/t-sql-queries/split-strings fournit une excellente analyse de performance pour les différentes options. Voici quelques-unes des options envisagées (copiées à partir du site pour référence):

CLR

.Net code à http://sqlblog.com/blogs/adam_machanic/archive/2009/04/28/sqlclr-string-splitting-part-2-even-faster-even-more-scalable.aspx.

CREATE ASSEMBLY CLRUtilities FROM 'c:\DLLs\CLRUtilities.dll' 
  WITH PERMISSION_SET = SAFE;
GO

CREATE FUNCTION dbo.SplitStrings_CLR
(
   @List      NVARCHAR(MAX),
   @Delimiter NVARCHAR(255)
)
RETURNS TABLE ( Item NVARCHAR(4000) )
EXTERNAL NAME CLRUtilities.UserDefinedFunctions.SplitString_Multi;
GO

XML

CREATE FUNCTION dbo.SplitStrings_XML
(
   @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

CTE

CREATE FUNCTION dbo.SplitStrings_CTE
(
   @List       NVARCHAR(MAX),
   @Delimiter  NVARCHAR(255)
)
RETURNS @Items TABLE (Item NVARCHAR(4000))
WITH SCHEMABINDING
AS
BEGIN
   DECLARE @ll INT = LEN(@List) + 1, @ld INT = LEN(@Delimiter);

   WITH a AS
   (
       SELECT
           [start] = 1,
           [end]   = COALESCE(NULLIF(CHARINDEX(@Delimiter, 
                       @List, @ld), 0), @ll),
           [value] = SUBSTRING(@List, 1, 
                     COALESCE(NULLIF(CHARINDEX(@Delimiter, 
                       @List, @ld), 0), @ll) - 1)
       UNION ALL
       SELECT
           [start] = CONVERT(INT, [end]) + @ld,
           [end]   = COALESCE(NULLIF(CHARINDEX(@Delimiter, 
                       @List, [end] + @ld), 0), @ll),
           [value] = SUBSTRING(@List, [end] + @ld, 
                     COALESCE(NULLIF(CHARINDEX(@Delimiter, 
                       @List, [end] + @ld), 0), @ll)-[end]-@ld)
       FROM a
       WHERE [end] < @ll
   )
   INSERT @Items SELECT [value]
   FROM a
   WHERE LEN([value]) > 0
   OPTION (MAXRECURSION 0);

   RETURN;
END
GO

Function

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;

il s'avère que la meilleure performance vient de l'utilisation de la fonction CLR alors que la solution XML fait aussi bien. Dans presque tous les cas, l'utilisation d'un tableau des nombres (l'approche n'était pas (voir ci-dessus) conduisent à la pire performance.

SELECT * FROM string_split('Hello John Smith', ' ')

Sortie

+-------+
| value |
+-------+
| Hello |
| John  |
| Smith |
+-------+

avez-vous regardé la table des nombres?

http://www.sqlservercentral.com/articles/T-SQL/62867/

http://bradsruminations.blogspot.com/2010/08/integer-list-splitting-sql-fable.html

je prendrais juste une des nombreuses fonctions qui crée une table et au lieu de lui rendre la valeur la mette dans une variable de table. Puis utilisez la variable table. Voici un exemple qui renvoie un tableau.

http://www.codeproject.com/KB/database/SQL_UDF_to_Parse_a_String.aspx

utiliser un UDF a le plus de sens c'est flexible pour tous les projets, celui que j'utilise généralement est sur mon blog,

http://sqlthis.blogspot.com/2005/02/list-to-table.html

parce qu'il est écrit pour prendre votre chaîne de caractères et un délimiteur, il peut être n'importe quel caractère simple pour un délimiteur. J'ai écrit celui au lien ci-dessus, mais ensuite j'ai trouvé de nombreux sites publiant une solution similaire, de sorte que les parties peuvent avoir été inspirés à partir de forums que je suis un membre de...

ça fonctionne, et j'espère qu'il va travailler pour vous aussi.

modifier 2017-08-09 comme suggéré, j'ai cloné le bloc de code ci-dessous. merci!

CREATE FUNCTION udfListToTable (@HList VarChar(2000), @Delimiter CHAR(1))
RETURNS @ListTable TABLE (Field1 VARCHAR(6))
  AS
  BEGIN
  --By: Francisco Tapia
  --Date: 2/1/2005
  --Purpose: To convert a Comma delimited text to a Temp Variable table To help avoid dynamic sql
  -- Instead you can join the temp table or use it in your where clause if a field is IN the subquery
       DECLARE @FieldText as VarChar(6)

       IF RIGHT(RTRIM(@HLIST),1) <>@Delimiter
       SET @HList = @HList + @Delimiter

       WHILE CHARINDEX(@Delimiter, @HList) > 0
       BEGIN
            IF CHARINDEX(@Delimiter, @HList) > 0
            BEGIN
                 SELECT @FieldText =LEFT(@HList, CHARINDEX(@Delimiter, @HList)-1)
            END
       ELSE 
       BEGIN
            SELECT @FieldText = RTRIM(LTRIM(@HList))
       END
       --Insert into Variable Table
       INSERT INTO @ListTable(Field1)
       SELECT RTRIM(LTRIM(@FieldText))
       --Remove Item from list
       SELECT @HList = RIGHT(RTRIM(@HList), LEN(RTRIM(@HList)) -
            CHARINDEX(@Delimiter, @HList))
       END 
       RETURN
  END

 DECLARE @cols  AS NVARCHAR(max), 
        @Val   VARCHAR(100)='Hi- Hello break this-Wall', 
        @Deli  VARCHAR(50)='-', 
        @query AS NVARCHAR(max) 

SELECT @cols = Stuff((SELECT ',' + Quotename(id) 
                      FROM   (SELECT stringpieceid AS ID, 
                                     stringpiece 
                              FROM 
                     [Utility].[dbo].[Splitstringtotable](@Val, @Deli)) 
                             X 
                      FOR xml path(''), type).value('.', 'NVARCHAR(MAX)'), 1, 1, 
               '') 

SELECT @query = 
'SELECT * FROM (SELECT StringPieceID as ID,StringPiece from [Utility].[dbo].[SplitStringToTable](''' 
         + @Val + ''',''' + @Deli + '''))X PIVOT  (     MAX(StringPiece)     for [ID] in (' + @cols 
         + ') ) P' 

PRINT @query 

EXEC Sp_executesql 
  @query 

USE TRIAL
GO

CREATE TABLE DETAILS
(
ID INT,
NAME VARCHAR(50),
ADDRESS VARCHAR(50)
)

INSERT INTO DETAILS
VALUES (100, 'POPE-JOHN-PAUL','VATICAN CIT|ROME|ITALY')
,(240, 'SIR-PAUL-McARTNEY','NEWYORK CITY|NEWYORK|USA')
,(460,'BARRACK-HUSSEIN-OBAMA','WHITE HOUSE|WASHINGTON|USA')
,(700, 'PRESIDENT-VLADAMIR-PUTIN','RED SQUARE|MOSCOW|RUSSIA')
,(950, 'NARENDRA-DAMODARDAS-MODI','10 JANPATH|NEW DELHI|INDIA')

select [ID]
,[NAME]
,[ADDRESS]
,REPLACE(LEFT(NAME, CHARINDEX('-', NAME)),'-',' ') as First_Name
,CASE 
WHEN CHARINDEX('-',REVERSE(NAME))+ CHARINDEX('-',NAME) < LEN(NAME)
THEN  SUBSTRING(NAME, CHARINDEX('-', (NAME)) + 1, LEN(NAME) - CHARINDEX('-' 
, REVERSE(NAME)) - CHARINDEX('-', NAME))
  ELSE 'NULL
  END AS Middle_Name
,REPLACE(REVERSE( SUBSTRING( REVERSE(NAME), 1, CHARINDEX('- 
',REVERSE(NAME)))), '-','') AS Last_Name 
,REPLACE(LEFT(ADDRESS, CHARINDEX('|', ADDRESS)),'|',' ') AS Locality
,CASE 
    WHEN CHARINDEX('|',REVERSE(ADDRESS))+ CHARINDEX('|',ADDRESS) < 
  LEN(ADDRESS) 
 THEN SUBSTRING(ADDRESS, CHARINDEX('|', (ADDRESS))+1, LEN(ADDRESS)- 
CHARINDEX('|', REVERSE(ADDRESS))-CHARINDEX('|',ADDRESS))
  ELSE 'Null' 
END AS STATE
,REPLACE(REVERSE(SUBSTRING(REVERSE(ADDRESS),1 
,CHARINDEX('|',REVERSE(ADDRESS)))),'|','') AS Country
 FROM DETAILS
  SELECT CHARINDEX('-', REVERSE(NAME)) AS LAST,CHARINDEX('-',NAME)AS FIRST, 
 LEN(NAME) AS LENGTH
 FROM DETAILS

-- LET ME KNOW IF YOU HAVE DOUBTS UNDERSTANDING THE CODE

Voici une fonction d'analyse définie par l'utilisateur qui permet au serveur SQL de fonctionner de la même manière que la fonction "Split" VB. Conçu pour l'optimisation interactive; par exemple, pour analyser des données dans une procédure stockée appelée à partir d'une API externe.

https://gallery.technet.microsoft.com/scriptcenter/User-def-function-enabling-98561cce