Splitting the string in sql server

Question

I have a string in the database which is comma separated.Like 'apple,banana,pineapple,grapes' I need to split this string on the basis of comma and iterate through this.Since there is no built in function in sql server, Is there any efficient way in which this objective can be attained.

Solution 1

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

Solution 2

... 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.

Solution 3

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

Comments

[dev7]

What server side language are you using?

[user3137901]

I would use SET @DelSize = LEN(@Delimiter + 'x') - 1 to handle delimiters with trailing blanks such as ' ' and ', '.

[SajithG]

Thank you very much Vignesh Kumar, great help !

[callisto]

@ChryCheng - That is definitely something that should be included in this solution.

[Aleks G]

I know this is fairly old - but thanks nonetheless. The only issue I have is when the substrings have embedded escaped delimiter, e.g. "Mickey\,Mouse, Goofy..." - this would split "Mickey\" and "Mouse" into two - but should only give me "Mickey,Mouse" as one