Comment puis-je Diviser une Chaîne Délimitée dans SQL Server Sans la Création d'une Fonction?

Je travaille avec une base de données SQL Server. J'ai une colonne qui contient une liste délimitée par des virgules, et j'ai besoin d'écrire une requête qui fend les valeurs de la liste en lignes. À partir de la navigation sur StackOverflow et le reste du web, je sais que c'est un problème commun. En fait, j'ai trouvé une analyse approfondie ici:

http://www.sommarskog.se/arrays-in-sql.html

Malheureusement, toutes les solutions que j'ai vu sur ce site et ailleurs, me demande de créer une fonction. Ce n'est pas une option pour moi, je manque les privilèges requis pour utiliser la commande CRÉER.

Sans CRÉER, je sais que je peux utiliser le PARSENAME fonction, quelque chose comme cela (Merci à Nathan Bedford à Comment puis-je diviser une chaîne afin que je puisse accéder à l'élément de x?.):

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

Cependant, PARSENAME fonctionne uniquement pour les listes de 4 points ou moins. Ma question est donc la suivante: Comment dois-je écrire une requête pour diviser une chaîne délimitée de plus de 4 éléments sans créer de nouveaux objets dans la base de données?

EDIT:

Merci à tous pour les réponses rapides. J'ai peut-être laissé de côté certaines informations importantes, je suis en interaction avec la base de données via une connexion ODBC. En plus de CRÉER des états, il semble y avoir d'autres déclarations qui ne fonctionnent pas. Par exemple, je n'arrive pas à utiliser DÉCLARER, dans une déclaration à définir une variable qui sera utilisée dans une autre déclaration. Aussi loin que je peux comprendre, je dois tout mettre dans une seule instruction SELECT (bien qu'AVEC semble également fonctionner pour la déclaration commune des tables). Malheureusement, toutes les solutions proposées à ce jour semblent exiger des déclarations de variable à l'extérieur de l'instruction SELECT, et qui ne fonctionne pas. S'il vous plaît garder avec moi-je suis en train d'apprendre que je vais.

OriginalL'auteur Todd | 2011-04-19

sql-server

Une version à l'aide de 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)

À l'aide d'une table au lieu
Remplacer @T avec ce que jamais la table où vous êtes en utilisant.

-- 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)

Le fractionnement de la chaîne de 'Hello John Smith' sans l'aide d'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)

BTW premier à l'aide de XML a une très mauvaise performance avec d'assez grands tableaux
Pourrait être amélioré par le casting pas dans la requête et de l'utilisation d'une variable xml à la place. Ne sera pas aussi rapide que d'utiliser un numberstable. SQL Violon
Je ne suis pas bon à cela, vous serais reconnaissant si vous montrer comment le faire sans casting?
Il suffit de suivre le lien vers SQL Violon dans mon dernier commentaire.
Pourriez-vous svp mettre à jour SQL violon lien si disponible. Si non, jamais l'esprit, merci pour la réponse quand même

OriginalL'auteur Mikael Eriksson

exemple en utilisant le construit en maître..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': Découper Une Chaîne En Utilisant Un Numéro De Table

OriginalL'auteur SQLMenace

Vous pouvez utiliser l'expression de table commune récursive progressivement à extraire un élément

Exemple de table

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, qui est ce qui est recherché par CHARINDEX. Le point ' .' est nécessaire parce que SQL Server n'a normalement pas voir les espaces à droite comme significative.
TOUTES les colonnes de la table d'origine peut être conservé dans le CTE, il suffit d'ajouter. Ici, je montre un exemple de 2 colonnes a et b conservés dans la sortie, avec la colonne c divisé en single et une colonne supplémentaire pour indiquer la position.

OriginalL'auteur RichardTheKiwi

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

CLR

.Net code à http://dataeducation.com/sqlclr-string-splitting-part-2-even-faster-even-more-scalable/.

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

Fonction

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, la meilleure performance est obtenue à l'aide de la fonction CLR tandis que le XML solution fait aussi bien. Dans presque tous les cas, à l'aide d'un tableau de nombres (l'approche n'est pas dupliqué ci-dessus) conduire à la pire performance.

Être prudent. Comme j'ai essayé de prévenir l'auteur de cet article, il utilise dupliqué en double des données pour tous les 10 000 lignes de données de test. C'est quelque chose qui ne sont pas généralement se produire dans la vie réelle et il biaise les résultats de l'essai en faveur de la réalité terriblement paresseux XML séparateur qui, d'ailleurs, ne pas manipuler le XML de caractères spéciaux.

OriginalL'auteur erdomke

Pour sql Server >= 2016 vous pouvez utiliser string_split comme ci-dessous:

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

Sortie

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

OriginalL'auteur Kannan Kandasamy

0

Avez-vous regardé le tableau de nombres?

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

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

OriginalL'auteur Sankar Reddy
0

Je voudrais juste prendre l'une des nombreuses fonctions qui crée une table et au lieu de retourner la valeur de la mettre dans une variable de table. Ensuite, utilisez la variable de table. Voici un exemple qui renvoie un tableau.

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

OriginalL'auteur Hogan

à l'aide d'une UDF qui fait le plus de sens, elle est flexible pour tous les projets, celui que j'utilise généralement est à mon blog,

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

parce que c'est écrit pour prendre votre chaîne d'entrée et un séparateur, il peut être n'importe quel caractère unique pour un délimiteur. J'ai écrit le lien ci-dessus, mais ensuite j'ai trouvé de nombreux sites de publication d'une solution similaire, afin que les parties peuvent avoir été inspirés de forums que je suis un membre de...

il 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

OriginalL'auteur SeeCoolGuy

 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

si c'est le délimiteur puis il le diviser en 3 parties et si non, alors que dans une seule partie

OriginalL'auteur Vikram Singh

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

-- LAISSEZ-MOI SAVOIR SI VOUS AVEZ DES DOUTES COMPRENDRE LE CODE

OriginalL'auteur URMIL PREMAL SHAH

0

Voici un définis par l'utilisateur fonction d'analyse syntaxique qui permet à SQL Server qui effectue également de même pour le VB "Split" de la fonction. Conçu pour interactive de l'effet de levier par exemple, pour analyser des données à l'intérieur d'une Procédure Stockée appelée à partir d'une API externe.

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

OriginalL'auteur Big D

Vous devez vous connecter pour publier un commentaire.