There is no built-in
function to split a delimited string in Microsoft SQL Server to split a string
to a table; we need to create our own function or sp or sql script. Converting
a delimited string into a data set or transforming it into useful data can be
an extremely useful tool when working with complex inputs or user-provided
data. There are many methods available to accomplish this task; here we will
discuss many of them, comparing performance, accuracy, and availability.
Using Table-Valued Function
Here I am creating a function which accepts the string which
is separated by delimiter and delimiter value.
I am creating a function ‘Split_a_string_to_table’
CREATE FUNCTION Split_a_string_to_table
(
@Str NVARCHAR(MAX),
@Delimiter
NCHAR(1)
)
RETURNS @Output TABLE (
List_Of_Item
NVARCHAR(1000)
)
AS
BEGIN
DECLARE
@StartIndex INT, @EndIndex INT
SET @StartIndex = 1
IF SUBSTRING(@Str, LEN(@Str) - 1, LEN(@Str)) <> @Delimiter
BEGIN
SET
@Str = @Str + @Delimiter
END
WHILE CHARINDEX(@Delimiter, @Str) > 0
BEGIN
SET
@EndIndex = CHARINDEX(@Delimiter, @Str)
INSERT
INTO @Output(List_Of_Item)
SELECT
SUBSTRING(@Str, @StartIndex, @EndIndex - 1)
SET
@Str = SUBSTRING(@Str, @EndIndex + 1, LEN(@Str))
END
RETURN
END
|
Create this function
Now we need to use this function where we want. See the
example.
I am passing the string as 'bagesh,kumar,singh,pune'
and delimiter as ‘,’
(comma) see the output.
See the another example
I am passing the string as 'bagesh|kumar|singh|pune'
and delimiter as ‘|’ (Pipe)
see the output.
We can try any delimiter
it is working fine.
Using XML Split a sting to table
Here a am creating a function which accept input as string
and delimiter and it will return table value
CREATE FUNCTION Split_a_string_to_table_using_XML
(
@Str NVARCHAR(MAX),
@Delimiter NCHAR(1)
)
RETURNS TABLE AS
RETURN
SELECT Split.a.value('.', 'NVARCHAR(MAX)') AS Data FROM
(
SELECT
CAST ('<A>' + REPLACE(@Str, @Delimiter, '</A><A>') + '</A>' AS XML) AS Data
) AS A CROSS APPLY Data.nodes ('/A') AS Split(a);
|
I am using this function
I am passing the string as 'bagesh,kumar,singh,pune'
and delimiter as ‘,’
(comma) see the output.
See the another example
I am passing the string as 'bagesh|kumar|singh|pune'
and delimiter as ‘|’ (Pipe)
see the output.
