Showing posts with label Split Function. Show all posts
Showing posts with label Split Function. Show all posts

Wednesday, January 4, 2012

Split


CREATE FUNCTION dbo.Split
(
      @RowData nvarchar(2000),
      @SplitOn nvarchar(5)
) 
RETURNS @RtnValue table
(
      Id int identity(1,1),
      Data nvarchar(100)
)
AS 
BEGIN
      Declare @Cnt int
      Set @Cnt = 1

      While (Charindex(@SplitOn,@RowData)>0)
      Begin
            Insert Into @RtnValue (data)
            Select
                  Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

            Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
            Set @Cnt = @Cnt + 1
      End
     
      Insert Into @RtnValue (data)
      Select Data = ltrim(rtrim(@RowData))

      Return
END



Tuesday, January 3, 2012

SQL Server 2005: Split string XML Style


--Here's a Split function using XML datatype.

--It's preety neat and simple compared to all others that i've seen.

--Forget While Loops and recursive CTE's.

--Enter XML:


IF OBJECT_ID('dbo.Split') IS NOT NULL
    DROP FUNCTION dbo.Split

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

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

DECLARE @text NVARCHAR(max)
SELECT @text = REPLICATE('ab,', 300) + 'ab'

SELECT * FROM dbo.Split(@text, ',')