Database

CREATE FUNCTION [dbo].[Split](@data NVARCHAR(MAX), @delimiter NVARCHAR(5))
RETURNS @t TABLE (data NVARCHAR(max))
AS
BEGIN
    
    DECLARE @textXML XML;
    SELECT    @textXML = CAST('<d>' + REPLACE(@data, @delimiter, '</d><d>') + '</d>' AS XML);

    INSERT INTO @t(data)
    SELECT  T.split.value('.', 'nvarchar(max)') AS data
    FROM    @textXML.nodes('/d') T(split)
    
    RETURN
END

CREATE FUNCTION [dbo].[Split](@data NVARCHAR(MAX), @delimiter NVARCHAR(5))<br />
RETURNS @t TABLE (data NVARCHAR(max))<br />
AS<br />
BEGIN<br />
    <br />
    DECLARE @textXML XML;<br />
    SELECT    @textXML = CAST('<d>' + REPLACE(@data, @delimiter, '</d><d>') + '</d>' AS XML);<br />
<br />
    INSERT INTO @t(data)<br />
    SELECT  T.split.value('.', 'nvarchar(max)') AS data<br />
    FROM    @textXML.nodes('/d') T(split)<br />
    <br />
    RETURN<br />
END<br />
<br />

ramyasangeet wrote:

1.the reason for the rows order difference.(is it because of execution plan?)

ramyasangeet wrote:

2.The resulting data of sql2005 should be in same order of sql 2000.