ways to substring a string with a delimiter - sql

here is the code for substring a string with specific delimiter in sql

DECLARE @data NVARCHAR(MAX),
@delimiter NVARCHAR(5)
SELECT @data = 'duplicate@Createrrrm@dddfdfddfdf',
@delimiter = '@'
DECLARE @textXML XML;
SELECT @textXML = CAST('' + REPLACE(@data, @delimiter, '') + '' AS XML);
--SELECT @textXML
SELECT T.split.value('.', 'nvarchar(max)') AS data
FROM @textXML.nodes('/d') T (split)
/* doing it through a function */
select * from [dbo].fn_ParseDelimitedStrings ('duplicate@Createrrrm@dddfdfddfdf','@')

Function Code:

Create Function [dbo].[fn_ParseDelimitedStrings]

(@String nvarchar(3500), @Delimiter char(1))

Returns @Values Table

(

RowId int Not Null Identity(1,1) Primary Key

,Value nvarchar(255) Not Null

)

As

Begin

Declare @startPos smallint

,@endPos smallint

If (Right(@String, 1) != @Delimiter)

Set @String = @String + @Delimiter

Set @startPos = 1

Set @endPos = CharIndex(@Delimiter, @String)

While @endPos > 0

Begin

Insert @Values(Value)

Select LTrim(RTrim(SubString(@String, @startPos, @endPos - @startPos)))

-- remove the delimiter just used

Set @String = Stuff(@String, @endPos, 1, '')

-- move string pointer to next delimiter

Set @startPos = @endPos

Set @endPos = CharIndex(@Delimiter, @String)

End

Return

End