Treeless Software SQL example returns a table with parsed elements in a passed string

Returns a table with parsed elements in a passed string.

USE [DB_Name]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[uft_ParseString] (
    @linetoparse VARCHAR(2000), 
    @delimeter CHAR(1)
)
RETURNS @elements TABLE (
    ElementID INT IDENTITY (1,1), 
    Element VARCHAR(2000)
)
AS
Begin
    Declare
        @LineIn VARCHAR(2000),
        @Element VARCHAR(2000),
        @StartPos INT,
        @Length INT
    Set @LineIn = @linetoparse
    While LEN(@LineIn) > 0
        Begin
            Set @StartPos = CHARINDEX(@delimeter, @LineIn)
            If @StartPos < 0 Set @StartPos = 0
            Set @Length = LEN(@LineIn) - @StartPos - 1
            If @Length < 0 Set @Length = 0
            If @StartPos > 0
                Begin
                    Set @Element = SUBSTRING(@LineIn, 1, @StartPos - 1)
                    Set @LineIn = SUBSTRING(@LineIn, @StartPos + 1, LEN(@LineIn) - @StartPos)
                End
            Else
                Begin
                    Set @Element = @LineIn
                    Set @LineIn = ''
                End
            Insert Into @elements(Element) Select @Element
        End
    Return 
End

Treeless Software

PC and Web Software Development.

Latest News

New VB.Net and SQL Examples

New Website Launched

July 7, 2011

Useful Links

Search