In almost every project it is
required to split comma separated string in to multiple rows. Sometimes it is
fixed that data is separated by comma but in some cases delimiter is something
other than comma. So I have created one function that will split data by
delimiter you specified. So it is not required to use comma only.
Split function has two parameters.
First one is data with delimiter
(i.e. comma) and
Second one is delimiter through
which you want to split data.
/* This function Separates data and return datatable with column named 'item' having type varchar.
** Note that here you can specify delimiter */
CREATE FUNCTION dbo.Split(
@StringToSeparate VARCHAR(8000), -- List of items that neeed to Separate
@Delimiter VARCHAR(8000) = ',' -- Delimiter that separates items from string
) RETURNS @List TABLE (item VARCHAR(8000))
BEGIN
DECLARE @Item VARCHAR(8000)
--Loop through string untill specified delimiter is found
WHILE CHARINDEX(@Delimiter,@StringToSeparate,0) <> 0
BEGIN
--Select the data
SELECT @Item=RTRIM(LTRIM(SUBSTRING(@StringToSeparate,1,CHARINDEX(@Delimiter,@StringToSeparate,0)-1)),
@StringToSeparate=RTRIM(LTRIM(SUBSTRING(@StringToSeparate,CHARINDEX(@Delimiter,@StringToSeparate,0)+LEN(@Delimiter),LEN(@StringToSeparate))))
-- For remove leading and traiing space we have use LTRIM and RTRIM function
IF LEN(@Item) > 0
-- If data is found insert in to table, that is going to return
INSERT INTO @List SELECT @Item
END
IF LEN(@StringToSeparate) > 0
-- Insert last item in table.
INSERT INTO @List SELECT @StringToSeparate
RETURN
END
GO
Normal Example:
select * from Split('1,22,333,', ',')
Output:
Other Example:
--Convert return value as integer
select CAST(item as int) as IntegerItem from Split('1,22,333,4444,', ',')
Output:
Third Example:
--Using different Delimiter, note that here '$'is embadded in string
select * from Split('String1$$String2$String2$$String3','$$')
Output:
Suggestions are most welcome.
Regards,
Shiva