I have a number of string fields in a report that I need to split into their component parts. The fields can contain differing number of component parts using different delimiters, so for each fields I'd like to pass in a set of delimiters which would then split the string, e.g.
String = '0=0, 4000=7200.0'
Delimiters '=',', ','='
Split values = 0,0,4000,7200.00
Is this possible, i.e. to have a generic split function that I can pass different delimiters in to?
Note that not all the comments parts are numeric, but the patterns for each field are predictable
SELECT Item
FROM dbo.'plitString('string1,'string,....')
or you can use split string function in stored procedure. Here I am writing a sample of string function in stored procedure.
CREATE PROCEDURE GetEmployees
@EmployeeIds VARCHAR(100)
AS
BEGIN
SELECT FirstName, LastName
FROM Employees
WHERE EmployeeId IN(
SELECT CAST(Item AS INTEGER)
FROM dbo.SplitString(@EmployeeIds, ',')
)
END
Just to be sure... There is no "dbo.SplitString" function in T-SQL. Please post the code for it because, if it's the one I'm thinking of, it's either got a WHILE Loop in it or is using the rather slothful "XML" method.
Actually, If you read OP requirement carefully, the normal standard split function does not work. Will need to cook up something new. Probably can use one of the split function as a base
Understood and agreed. I did previously read the requirement and I did see that there are multiple delimiters necessary. If first wanted to make sure that folks know there is no native SplitString function and also wanted to see what that function consisted of.
I also modify the function fnParseString() a bit to return the entire string if the delimiter is empty. This is to cater for the last section
DECLARE @NextPos SMALLINT,
@LastPos SMALLINT,
@Offset TINYINT = DATALENGTH(@Delimiter),
@Found SMALLINT;
-- ## Added these 2 lines ## here
IF @Offset = 0
RETURN @Text
IF @Section > 0
SELECT @Text = REVERSE(@Text),
@Delimiter = REVERSE(@Delimiter);
the Final Query. Sorry the formatting is out. http://poorsql.com/ can't process the query
declare @String varchar(100) = '0=0, 4000=7200.0'
declare @Delimiters varchar(10) = '=|, |=' -- using Pipe as delimiter
; with delimiters as
(
-- parse the delimiter to rows
select RowID, Delimiter = Data
from fnParseList('|', @Delimiters + '|') -- append the pipe so that it will return the last section
),
rcte as
(
-- recursive CTE
select d.RowID, d.Delimiter,
Val = dbo.fnParseString(-1, d.Delimiter, @String),
String = stuff(@String, 1, datalength(dbo.fnParseString(-1, d.Delimiter, @String)) + datalength(d.Delimiter), '')
from delimiters d
where d.RowID = 1
union all
select d.RowID, d.Delimiter,
Val = dbo.fnParseString(-1, d.Delimiter, r.String),
String = stuff(r.String, 1, datalength(dbo.fnParseString(-1, d.Delimiter, r.String)) + datalength(d.Delimiter), '')
from rcte r
inner join delimiters d on r.RowID = d.RowID - 1
)
select *
from rcte
My recommendation would be to avoid any splitter code that contains a WHILE loop and/or lives in an mTVF (multi-statement Table Valued Function). You can do much the same with the splitter that Gerald cited without the performance hit. If you need a splitter that handles larger than NVARCHAR(4000) or VARCHAR(8000), I strongly recommend a good (hard to find, actually) SQLCLR splitter.