Can you write a split function for SQL?

Question

I have a string that comes back and has a "_" as delimiter between the fields in it:

string = "A_B_C_D"

I would like to write an SQL function that I can pass string to and get back any of the components so:

split(string,0) would return A

and

split(string,2) would retunr C

Thanks

Answer 1

This works with MSSQL (TSQL)
You can modify it so that a longer string is accepted.
You can also internally modify the delimiter to something other than an underscore, or you could add it as a parameter.
If you were to modify the delimiter to something other than a single character, you would have to then modify the substring statements to compensate.
There is probably a more elegant way to do this. I'll keep an eye on this question to see if someone posts it, because I'd like to know.


CREATE function dbo.fn_split (@instring varchar(50), @i int)
returns varchar AS
begin

declare @outstring varchar(50)
declare @patposition int
declare @pat char
declare @x int

set @pat = '_'
set @x = 0

parse:
set @patposition = (select patindex('%'+@pat+'%',@instring))

if @i=@x
begin
set @outstring = substring(@instring,0,@patposition+1)
end
else begin
set @x=@x+1
set @instring = substring(@instring,@patposition+2,len(@instring))
goto parse
end

return @outstring
end