![]() |
T-SQL split string - Printable Version +- 0Day Forums (https://0day.red) +-- Forum: Coding (https://0day.red/Forum-Coding) +--- Forum: Database (https://0day.red/Forum-Database) +---- Forum: Microsoft SQL Server (https://0day.red/Forum-Microsoft-SQL-Server) +---- Thread: T-SQL split string (/Thread-T-SQL-split-string) |
RE: T-SQL split string - timalia113182 - 07-31-2023 I have developed a double Splitter (Takes two split characters) as requested [Here][1]. Could be of some value in this thread seeing its the most referenced for queries relating to string splitting. CREATE FUNCTION uft_DoubleSplitter ( -- Add the parameters for the function here @String VARCHAR(4000), @Splitter1 CHAR, @Splitter2 CHAR ) RETURNS @Result TABLE (Id INT,MId INT,SValue VARCHAR(4000)) AS BEGIN DECLARE @FResult TABLE(Id INT IDENTITY(1, 1), SValue VARCHAR(4000)) DECLARE @SResult TABLE(Id INT IDENTITY(1, 1), MId INT, SValue VARCHAR(4000)) SET @String = @String+@Splitter1 WHILE CHARINDEX(@Splitter1, @String) > 0 BEGIN DECLARE @WorkingString VARCHAR(4000) = NULL SET @WorkingString = SUBSTRING(@String, 1, CHARINDEX(@Splitter1, @String) - 1) --Print @workingString INSERT INTO @FResult SELECT CASE WHEN @WorkingString = '' THEN NULL ELSE @WorkingString END SET @String = SUBSTRING(@String, LEN(@WorkingString) + 2, LEN(@String)) END IF ISNULL(@Splitter2, '') != '' BEGIN DECLARE @OStartLoop INT DECLARE @OEndLoop INT SELECT @OStartLoop = MIN(Id), @OEndLoop = MAX(Id) FROM @FResult WHILE @OStartLoop <= @OEndLoop BEGIN DECLARE @iString VARCHAR(4000) DECLARE @iMId INT SELECT @iString = SValue+@Splitter2, @iMId = Id FROM @FResult WHERE Id = @OStartLoop WHILE CHARINDEX(@Splitter2, @iString) > 0 BEGIN DECLARE @iWorkingString VARCHAR(4000) = NULL SET @IWorkingString = SUBSTRING(@iString, 1, CHARINDEX(@Splitter2, @iString) - 1) INSERT INTO @SResult SELECT @iMId, CASE WHEN @iWorkingString = '' THEN NULL ELSE @iWorkingString END SET @iString = SUBSTRING(@iString, LEN(@iWorkingString) + 2, LEN(@iString)) END SET @OStartLoop = @OStartLoop + 1 END INSERT INTO @Result SELECT MId AS PrimarySplitID, ROW_NUMBER() OVER (PARTITION BY MId ORDER BY Mid, Id) AS SecondarySplitID , SValue FROM @SResult END ELSE BEGIN INSERT INTO @Result SELECT Id AS PrimarySplitID, NULL AS SecondarySplitID, SValue FROM @FResult END RETURN **Usage:** --FirstSplit SELECT * FROM uft_DoubleSplitter('ValueA=ValueB=ValueC=ValueD==ValueE&ValueA=ValueB=ValueC===ValueE&ValueA=ValueB==ValueD===','&',NULL) --Second Split SELECT * FROM uft_DoubleSplitter('ValueA=ValueB=ValueC=ValueD==ValueE&ValueA=ValueB=ValueC===ValueE&ValueA=ValueB==ValueD===','&','=') **Possible Usage (Get second value of each split):** SELECT fn.SValue FROM uft_DoubleSplitter('ValueA=ValueB=ValueC=ValueD==ValueE&ValueA=ValueB=ValueC===ValueE&ValueA=ValueB==ValueD===', '&', '=')AS fn WHERE fn.mid = 2 [1]: [To see links please register here] RE: T-SQL split string - foredescribe456688 - 07-31-2023 The easiest way to do this is by using `XML` format. **1. Converting string to rows without table** **QUERY** DECLARE @String varchar(100) = 'String1,String2,String3' -- To change ',' to any other delimeter, just change ',' to your desired one DECLARE @Delimiter CHAR = ',' SELECT LTRIM(RTRIM(Split.a.value('.', 'VARCHAR(100)'))) 'Value' FROM ( SELECT CAST ('<M>' + REPLACE(@String, @Delimiter, '</M><M>') + '</M>' AS XML) AS Data ) AS A CROSS APPLY Data.nodes ('/M') AS Split(a) **RESULT** x---------x | Value | x---------x | String1 | | String2 | | String3 | x---------x **2. Converting to rows from a table which have an ID for each CSV row** **SOURCE TABLE** x-----x--------------------------x | Id | Value | x-----x--------------------------x | 1 | String1,String2,String3 | | 2 | String4,String5,String6 | x-----x--------------------------x **QUERY** -- To change ',' to any other delimeter, just change ',' before '</M><M>' to your desired one DECLARE @Delimiter CHAR = ',' SELECT ID,LTRIM(RTRIM(Split.a.value('.', 'VARCHAR(100)'))) 'Value' FROM ( SELECT ID,CAST ('<M>' + REPLACE(VALUE, @Delimiter, '</M><M>') + '</M>' AS XML) AS Data FROM TABLENAME ) AS A CROSS APPLY Data.nodes ('/M') AS Split(a) **RESULT** x-----x----------x | Id | Value | x-----x----------x | 1 | String1 | | 1 | String2 | | 1 | String3 | | 2 | String4 | | 2 | String5 | | 2 | String6 | x-----x----------x RE: T-SQL split string - lycees916452 - 07-31-2023 There is a correct version on here but I thought it would be nice to add a little fault tolerance in case they have a trailing comma as well as make it so you could use it not as a function but as part of a larger piece of code. Just in case you're only using it once time and don't need a function. This is also for integers (which is what I needed it for) so you might have to change your data types. DECLARE @StringToSeperate VARCHAR(10) SET @StringToSeperate = '1,2,5' --SELECT @StringToSeperate IDs INTO #Test DROP TABLE #IDs CREATE TABLE #IDs (ID int) DECLARE @CommaSeperatedValue NVARCHAR(255) = '' DECLARE @Position INT = LEN(@StringToSeperate) --Add Each Value WHILE CHARINDEX(',', @StringToSeperate) > 0 BEGIN SELECT @Position = CHARINDEX(',', @StringToSeperate) SELECT @CommaSeperatedValue = SUBSTRING(@StringToSeperate, 1, @Position-1) INSERT INTO #IDs SELECT @CommaSeperatedValue SELECT @StringToSeperate = SUBSTRING(@StringToSeperate, @Position+1, LEN(@StringToSeperate)-@Position) END --Add Last Value IF (LEN(LTRIM(RTRIM(@StringToSeperate)))>0) BEGIN INSERT INTO #IDs SELECT SUBSTRING(@StringToSeperate, 1, @Position) END SELECT * FROM #IDs RE: T-SQL split string - huongsaliowb - 07-31-2023 I modified +Andy Robinson's function a little bit. Now you can select only required part from returning table: CREATE FUNCTION dbo.splitstring ( @stringToSplit VARCHAR(MAX) ) RETURNS @returnList TABLE ([numOrder] [tinyint] , [Name] [nvarchar] (500)) AS BEGIN DECLARE @name NVARCHAR(255) DECLARE @pos INT DECLARE @orderNum INT SET @orderNum=0 WHILE CHARINDEX('.', @stringToSplit) > 0 BEGIN SELECT @orderNum=@orderNum+1; SELECT @pos = CHARINDEX('.', @stringToSplit) SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1) INSERT INTO @returnList SELECT @orderNum,@name SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos) END SELECT @orderNum=@orderNum+1; INSERT INTO @returnList SELECT @orderNum, @stringToSplit RETURN END Usage: <code>SELECT Name FROM dbo.splitstring('ELIS.YD.CRP1.1.CBA.MDSP.T389.BT') WHERE numOrder=5 </code> RE: T-SQL split string - party52 - 07-31-2023 You can Use this function: CREATE FUNCTION SplitString ( @Input NVARCHAR(MAX), @Character CHAR(1) ) RETURNS @Output TABLE ( Item NVARCHAR(1000) ) AS BEGIN DECLARE @StartIndex INT, @EndIndex INT SET @StartIndex = 1 IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character BEGIN SET @Input = @Input + @Character END WHILE CHARINDEX(@Character, @Input) > 0 BEGIN SET @EndIndex = CHARINDEX(@Character, @Input) INSERT INTO @Output(Item) SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1) SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input)) END RETURN END GO RE: T-SQL split string - podophthalmous32000 - 07-31-2023 The easiest way: 1. Install SQL Server 2016 2. Use STRING_SPLIT [To see links please register here] It works even in express edition :). RE: T-SQL split string - nijinsky661 - 07-31-2023 Finally the wait is over in **SQL Server 2016** they have introduced Split string function : [**`STRING_SPLIT`**][1] select * From STRING_SPLIT ('a,b', ',') cs All the other methods to split string like XML, Tally table, while loop, etc.. has been blown away by this `STRING_SPLIT` function. Here is an excellent article with performance comparison : [**Performance Surprises and Assumptions : STRING_SPLIT**][2] [1]: [To see links please register here] [2]:[To see links please register here] RE: T-SQL split string - hanifiya381467 - 07-31-2023 **Here is an example that you can use as function or also you can put the same logic in procedure. --SELECT * from [dbo].[fn_SplitString]('red,blue,green',',') ;** CREATE FUNCTION [dbo].[fn_SplitString] (@CSV VARCHAR(MAX), @Delimeter VARCHAR(100) = ',') RETURNS @retTable TABLE ( [value] VARCHAR(MAX) NULL )AS BEGIN DECLARE @vCSV VARCHAR (MAX) = @CSV, @vDelimeter VARCHAR (100) = @Delimeter; IF @vDelimeter = ';' BEGIN SET @vCSV = REPLACE(@vCSV, ';', '~!~#~'); SET @vDelimeter = REPLACE(@vDelimeter, ';', '~!~#~'); END; SET @vCSV = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@vCSV, '&', '&amp;'), '<', '&lt;'), '>', '&gt;'), '''', '&apos;'), '"', '&quot;'); DECLARE @xml XML; SET @xml = '<i>' + REPLACE(@vCSV, @vDelimeter, '</i><i>') + '</i>'; INSERT INTO @retTable SELECT x.i.value('.', 'varchar(max)') AS COLUMNNAME FROM @xml.nodes('//i')AS x(i); RETURN; END; RE: T-SQL split string - largess320 - 07-31-2023 All the functions for string splitting that use some kind of Loop-ing (iterations) have bad performance. They should be replaced with set-based solution. This code executes excellent. CREATE FUNCTION dbo.SplitStrings ( @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 RE: T-SQL split string - kauri326 - 07-31-2023 The often used approach with XML elements breaks in case of forbidden characters. This is an approach to use this method with any kind of character, even with the semicolon as delimiter. The trick is, first to use `SELECT SomeString AS [*] FOR XML PATH('')` to get all forbidden characters properly escaped. That's the reason, why I replace the delimiter to a *magic value* to avoid troubles with `;` as delimiter. DECLARE @Dummy TABLE (ID INT, SomeTextToSplit NVARCHAR(MAX)) INSERT INTO @Dummy VALUES (1,N'A&B;C;D;E, F') ,(2,N'"C" & ''D'';<C>;D;E, F'); DECLARE @Delimiter NVARCHAR(10)=';'; --special effort needed (due to entities coding with "&code;")! WITH Casted AS ( SELECT * ,CAST(N'<x>' + REPLACE((SELECT REPLACE(SomeTextToSplit,@Delimiter,N'§§Split$me$here§§') AS [*] FOR XML PATH('')),N'§§Split$me$here§§',N'</x><x>') + N'</x>' AS XML) AS SplitMe FROM @Dummy ) SELECT Casted.ID ,x.value(N'.',N'nvarchar(max)') AS Part FROM Casted CROSS APPLY SplitMe.nodes(N'/x') AS A(x) The result ID Part 1 A&B 1 C 1 D 1 E, F 2 "C" & 'D' 2 <C> 2 D 2 E, F |