Splitting the string in sql server
... Since there is no built in function in sql server ...
That was true at the time you asked this question but SQL Server 2016 introduces STRING_SPLIT
.
So you can just use
SELECT value
FROM STRING_SPLIT ('apple,banana,pineapple,grapes', ',')
There are some limitations (only single character delimiters accepted and a lack of any column indicating the split index being the most eye catching). The various restrictions and some promising results of performance testing are in this blog post by Aaron Bertrand.
Try this function
CREATE FUNCTION [dbo].[func_Split]
(
@DelimitedString varchar(8000),
@Delimiter varchar(100)
)
RETURNS @tblArray TABLE
(
ElementID int IDENTITY(1,1), -- Array index
Element varchar(1000) -- Array element contents
)
AS
BEGIN
-- Local Variable Declarations
-- ---------------------------
DECLARE @Index smallint,
@Start smallint,
@DelSize smallint
SET @DelSize = LEN(@Delimiter)
-- Loop through source string and add elements to destination table array
-- ----------------------------------------------------------------------
WHILE LEN(@DelimitedString) > 0
BEGIN
SET @Index = CHARINDEX(@Delimiter, @DelimitedString)
IF @Index = 0
BEGIN
INSERT INTO
@tblArray
(Element)
VALUES
(LTRIM(RTRIM(@DelimitedString)))
BREAK
END
ELSE
BEGIN
INSERT INTO
@tblArray
(Element)
VALUES
(LTRIM(RTRIM(SUBSTRING(@DelimitedString, 1,@Index - 1))))
SET @Start = @Index + @DelSize
SET @DelimitedString = SUBSTRING(@DelimitedString, @Start , LEN(@DelimitedString) - @Start + 1)
END
END
RETURN
END
Example Usage – simply pass the function the comma delimited string as well as your required delimiter.
DECLARE @SQLStr varchar(100)
SELECT @SQLStr = 'Mickey Mouse, Goofy, Donald Duck, Pluto, Minnie Mouse'
SELECT
*
FROM
dbo.func_split(@SQLStr, ',')
Result will be like this
You can convert your data to XML, by replacing the comma by a custom tag, in this case, <w>
for word.
create table t(col varchar(255));
insert into t values ('apple,banana,pineapple,grapes');
insert into t values ('car,bike,airplane');
select cast(('<w>' + replace(col, ',', '</w><w>') + '</w>') as xml) as xmlValue
from t
Which returns
| XMLVALUE |
|--------------------------------------------------------|
| <w>apple</w><w>banana</w><w>pineapple</w><w>grapes</w> |
| <w>car</w><w>bike</w><w>airplane</w> |
Now, if you use this query as a inner xml select, the outer query can split it into distinct rows:
Solution:
select split.xmlTable.value('.', 'varchar(255)') as xmlValue
from (
select cast(('<w>' + replace(col, ',', '</w><w>') + '</w>') as xml) as xmlValue
from t
) as xmlTable
cross apply xmlValue.nodes ('/w') as split(xmlTable);
Live sqlFiddle