String Split

create function fnStringSplit

(

       @str  Varchar(100),

       @delimiter  Varchar(1)

)

returns @Temp  Table ( item varchar(100))

AS

begin

--Declare @str as Varchar(100) = 'DXB-DEL/DEL-DXB/DEL-HYD/HYD-BLR'

--Declare @delimiter As Varchar(1)='/'

Declare @i as int=0

Declare @j as int=0

Set @j = (Len(@str) - len(REPLACE(@str,@delimiter,'')))

While @i  < = @j

Begin

  if @i  < @j

  Begin

      Insert into @Temp

      Values(SUBSTRING(@str,1,Charindex(@delimiter,@str,1)-1))

      set @str = right(@str,(len(@str)- Charindex(@delimiter,@str,1)))

  End

  Else

  Begin

     Insert into @Temp Values(@str)

  End

 Set @i = @i + 1

End

--Select * from @Temp

return

end

 

select dbo.fnStringSplit('DXB-DEL/DEL-DXB/DEL-HYD/HYD-BLR','/')