-- DBO. fnsplittovarchar
If exists (select * From information_schema.routines where routine_name = 'fnsplittovarchar ')
Begin
Drop function fnsplittovarchar
If not exists (select * From information_schema.routines where routine_name = 'fnsplittovarchar ')
Print 'drop function fnsplittovarchar succeeded'
Else
Print 'drop function fnsplittovarchar failed'
End
Else
Print 'attempting to create function dbo. fnsplittovarchar'
Go
Create Function DBO. fnsplittovarchar
(
@ Sourcesql varchar (8000 ),
@ Strseprate varchar (10)
)
Returns @ temp table (splitvalue varchar (500 ))
As
Begin
Declare @ I int
Declare @ strsepratelength int
Set @ strsepratelength = Len (@ strseprate)
Set @ sourcesql = rtrim (ltrim (@ sourcesql ))
Set @ I = charindex (@ strseprate, @ sourcesql)
While @ I> = 1
Begin
If @ I = 1
Insert @ temp values ('')
Else
Insert @ temp values (left (@ sourcesql, @ i-1 ))
Set @ sourcesql = substring (@ sourcesql, @ I + @ strsepratelength, Len (@ sourcesql)-@ I-@ strsepratelength + 1)
Set @ I = charindex (@ strseprate, @ sourcesql)
End
If @ sourcesql <> @ strseprate
Insert @ temp values (@ sourcesql)
Return
End
Go
If exists (select * From information_schema.routines where routine_name = 'fnsplittovarchar ')
Print 'create function DBO. fnsplittovarchar succeeded'
Else
Print 'create function DBO. fnsplittovarchar failed'
Go