Mimic STRING_SPLIT without custom function in SQL Server 2008

Question

It is asked many times, but not this way.

I am on SQL Server 2008, and there is no STRING_SPLIT function (like in 2016).

A query returns with the following row, see below a single example row. What you see below in bald is a single field actually, so one varchar column has it altogether:

Appple|10|admin|845687|Apr|26|11:32:29|2016|AwesomeApplication.zip

which I'd like to be split by the pipe | character.

I cannot write a CTE for this, or a custom function.

I have to extract the individual pipe delimited elements, into different columns, within one select statement using the built in string functions like CHARINDEX, PATINDEX.

Does anybody have any idea?

If its a single string just copy the code from a while-loop split function and use it inline, there will be a point at which each token is exposed — Alex K.
– Alex K., Commented May 6, 2016 at 17:29
@AlexK. edited the question to make it more clear ... it actually one field coming from a query — Avi
– Avi, Commented May 6, 2016 at 17:40

Stan Shaw · Accepted Answer · 2016-05-06 17:43:45Z

7

DECLARE @Result Table(Value varchar(50))
DECLARE @x XML
SELECT @X = CAST('<A>' + REPLACE(@StringList, '|', '</A><A>') + '</A>' AS XML)

INSERT INTO @Result
SELECT t.value('.', 'varchar(50)') as inVal
FROM @X.nodes('/A') AS x(t)

This will create a table with one column (Value). Each split value from your pipe-delimited string will create a new record in this table. Then you can join to it however you'd like. Please let me know if this is unclear or if it doesn't work on SQL 2008.

You can increase the size of the varchar, if needed - and you can modify the query to split on different values (comma-delimited, etc.).

answered May 6, 2016 at 17:43

Stan Shaw

3,0441 gold badge14 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Avi Over a year ago

this gave me the idea!

Alex Kudryashev · Accepted Answer · 2016-05-06 19:04:27Z

0

ALTER FUNCTION [dbo].[split] 
(
    @string varchar(max), 
    @separator varchar(1) -- longer separator is also possible
)
RETURNS 
@result TABLE (keyword varchar(max) )
AS
BEGIN
declare @pos int=0, @res varchar(100)
while len(@string)>0
begin
set @pos=CHARINDEX(@separator, @string+@separator,0)
select @res=left(@string,@pos),@string=SUBSTRING(@string,@pos+1,100)
insert @result values(@res)
end
    RETURN 
END

answered May 6, 2016 at 19:04

Alex Kudryashev

9,5103 gold badges31 silver badges39 bronze badges

Comments

Lockszmith · Accepted Answer · 2020-10-06 17:37:43Z

It's been a long while since this question was asked, and although the OP wanted a non-function solution, this is the only post where the answer pointed me in the right direction for me to write my code, so I thought I'd share my solution here.

What it does

This code, checks the master db compatibility mode and creates an appropriate _my_string_split function. If string_split exists, this is just a wrapper. If not it will use the method Stan proposed in the accepted answer

So now, regardless of where I'm running my code, after creating the function, all I need to do is use master.dbo._my_string_split, for example:

SELECT * FROM master.dbo._my_string_split('Hello|World!','|')

The Code

USE master
IF OBJECT_ID('dbo._my_string_split') IS NOT NULL DROP FUNCTION [dbo].[_my_string_split]
GO

DECLARE @sqlCode NVARCHAR(MAX);
IF 130 <= (SELECT compatibility_level FROM sys.databases WHERE name = DB_NAME()) 
    SET @sqlCode = '
    --- 130+ string_split exists
    CREATE FUNCTION [dbo].[_my_string_split]
    (
        @string nvarchar(max),
        @separator nvarchar(1)
    )
    RETURNS
    @result TABLE ([Value] nvarchar(max) )
    BEGIN   

        INSERT INTO @result
            SELECT [Value] FROM string_split(@string,@separator)

        RETURN

    END
'
ELSE
  SET @sqlCode = '
    --- before 130: string_split does not exists
    CREATE FUNCTION [dbo].[_my_string_split]
    (
        @string nvarchar(max),
        @separator nvarchar(1)
    )
    RETURNS
    @result TABLE ([Value] nvarchar(max) )
    BEGIN

        INSERT INTO @result
            SELECT [Value]=t.value(''.'', ''varchar(max)'')
            FROM (SELECT x=(CAST((''<A>'' + REPLACE(@string, @separator, ''</A><A>'') + ''</A>'') AS XML))) a
            CROSS APPLY a.x.nodes(''/A'') AS x(t)

        RETURN

    END
'

PRINT @sqlCode
EXEC sp_sqlexec @sqlCode

Hope others will find this useful.

Collectives™ on Stack Overflow

Mimic STRING_SPLIT without custom function in SQL Server 2008

3 Answers 3

1 Comment

Comments

What it does

The Code

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

What it does

The Code

Comments

Your Answer

Sign up or log in

Post as a guest

Related