Le moyen le plus efficace de split la string en rangées

J'utilise la fonction suivante pour split une string en lignes. Il est beaucoup plus rapide que la fonction précédente que j'utilisais, mais je dois en quelque sorte accélérer le traitement de ces données (c'est un travail ETL):

ALTER FUNCTION [dbo].[ArrayToTable] ( @InputSsortingng VARCHAR(MAX) = '' , @Delimitter VARCHAR(1) = ',' ) RETURNS @RESULT TABLE([Position] INT IDENTITY, [Value] VARCHAR(MAX)) AS BEGIN DECLARE @XML XML SELECT @XML = CONVERT(XML, SQL_TEXT) FROM ( SELECT '<root><item>' + REPLACE(@InputSsortingng, @Delimitter, '</item><item>') + '</item></root>' AS SQL_TEXT ) dt INSERT INTO @RESULT([Value]) SELECT t.col.query('.').value('.', 'VARCHAR(1000)') AS [Value] FROM @XML.nodes('root/item') t(col) RETURN END 

Quelqu'un peut-il penser à un moyen meilleur / plus rapide de transformer une string délimitée en lignes? J'utilise un cross apply sur ma requête pour join ces résultats.

Quelqu'un peut-il penser à une méthode plus efficace pour transformer les strings délimitées en lignes?

Voici la fonction la plus performante que j'ai:

 CREATE FUNCTION [Resource].[udf_SplitByXml] (@Data NVARCHAR(MAX), @Delimiter NVARCHAR(5)) RETURNS @Table TABLE ( Data NVARCHAR(MAX) , SequentialOrder INT IDENTITY(1, 1)) AS BEGIN DECLARE @TextXml XML; SELECT @TextXml = CAST('<d>' + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@Data, '&', '&amp;'), '<', '&lt;'), '>', '&gt;'), '"', '&quot;'), '''', '&apos;'), @Delimiter, '</d><d>') + '</d>' AS XML); INSERT INTO @Table (Data) SELECT Data = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(RTRIM(LTRIM(T.split.value('.', 'nvarchar(max)'))), '&amp;', '&'), '&lt;', '<'), '&gt;', '>'), '&quot;', '"'), '&apos;', '''') FROM @TextXml.nodes('/d') T(Split) RETURN END 

Voici des exemples d'appels que vous pouvez utiliser pour tester les résultats:

 SELECT * FROM Resource.udf_SplitByXml('yes, no, maybe, so', ','); SELECT * FROM Resource.udf_SplitByXml('who|what|where|when|why|how|Uh, I don''t know!', '|'); SELECT * FROM Resource.udf_SplitByXml('Government, Education, Non-profit|Energy & Power|Yes|No', '|'); SELECT * FROM Resource.udf_SplitByXml('Energy & Power|Some<Thing>Wicked''This"Way Comes', '|'); 

Une autre option consiste à essayer la solution CLR basée sur le code d'Adam Machanic qui a été le gagnant d'un test de performance dans ce blog .

Voici une autre fonction que je viens d'écrire qui, à ma grande surprise, est légèrement plus rapide que la méthode XML ci-dessus, mais juste à peine. La comparaison des deux fonctions pour traiter 1 000 lignes de strings délimitées ne donne aucune différence de performance. En traitant 50K lignes de strings délimitées, la méthode XML prend 129 secondes pour générer 435.217 lignes extraites, par rapport à 122 secondes pour générer 435.217 lignes extraites pour ma méthode de manipulation de string simple.

Ce n'est donc pas vraiment beaucoup plus rapide, même si je suppose que cela ferait une différence si vous traitez des centaines de milliers de lignes. Les principaux avantages de ma fonction sont qu'elle est facile à lire et à comprendre, qu'elle ne dépend pas des fonctionnalités XML susceptibles de changer dans les futures versions de SQL Server et qu'elle devrait être facilement transportable dans n'importe quelle langue. J'espérais vraiment find quelque chose de très rapide, mais je suppose que c'est le meilleur que nous pouvons faire pour l'instant.

  CREATE FUNCTION dbo.ufn_util_Split ( @RawText varchar(max), @SplitCharacter varchar(2) ) RETURNS @t_Results TABLE ( RowIndex int IDENTITY(1,1), RowValue varchar(max) ) AS BEGIN DECLARE @vc_RowValue varchar(max) = '' DECLARE @vc_Remainder varchar(max) = CASE WHEN RIGHT(@RawText,1) = @SplitCharacter THEN @RawText ELSE @RawText + @SplitCharacter END --the ssortingng must end in the split character in order for this to work DECLARE @int_SplitPosition int = 0 DECLARE @int_LenSplitChar int = 0 SELECT @int_LenSplitChar = LEN(@SplitCharacter) --determine the first segment to start with SELECT @int_SplitPosition = CHARINDEX(@SplitCharacter,@vc_Remainder) WHILE (@int_SplitPosition > 0) BEGIN SELECT @vc_RowValue = LEFT(@vc_Remainder,@int_SplitPosition-1) INSERT INTO @t_Results (RowValue) VALUES (@vc_RowValue) --now ssortingp off the segment we just extracted and determine where the next segment ends, and continue SET @vc_Remainder = SUBSTRING(@vc_Remainder,@int_SplitPosition+@int_LenSplitChar,LEN(@vc_Remainder)) SELECT @int_SplitPosition = CHARINDEX(@SplitCharacter,@vc_Remainder) CONTINUE END RETURN END 

Salut, Essayez ceci –

 create procedure sp_getAllItems @input varchar(100) as BEGIN create table #tmpFruits (name varchar(10)) Declare @Qry Varchar(500) Set @Qry = '' Select @Qry = @Qry + ' Insert into #tmpFruits ' Select @Qry = @Qry + Replace( 'Select ''' + Replace(Replace(Replace(Replace(@input,CHAR(9),''),' ',''),CHAR(10),''),CHAR(13),'') , ',',''' Union Select ''') + '''' Exec (@Qry) select * from Fruitstest where name in (select name from #tmpFruits) drop table #tmpFruits END exec sp_getAllItems @input = 'cherry,banana'