MySQL Forums
Forum List  »  Microsoft SQL Server

Split String
Posted by: Mike Sheldon
Date: March 27, 2006 10:10AM

Our MS SQL database extensively uses a T-SQL user defined function that splits a delimited string and returns a table of the split strings. We use this like

select * from table1 where MainID in (select token from splitstring(',',
    )

    Below is the code for the MS T-SQL function. Is there any way to do something similar in MySQL?

    CREATE FUNCTION ufSplitString
    (
    @InString varchar(8000),
    @Delim char(1)
    )
    RETURNS @Return table
    (
    Position int identity,
    Token varchar(100) -- Maximum token size is 100 chars...
    )
    As
    BEGIN
    Declare @CR varchar(1),
    @LF varchar(1)
    Set @CR = char(10)
    Set @LF = char(13)
    --
    If @InString is null return
    --
    Declare @Pos int
    Declare @Pattern char(3)
    Set @Pattern = '%' + @Delim + '%'
    --
    Declare @Token varchar(30)
    SELECT @InString = @InString + @Delim -- add trailing delimiter
    SELECT @Pos = PATINDEX(@Pattern, @InString)
    WHILE (@Pos <> 0) BEGIN
    SELECT @Token = ltrim(rtrim(SUBSTRING(@InString, 1, @Pos - 1)))
    Select @Token = replace(@Token, @CR, '')
    Select @Token = replace(@Token, @LF, '')
    Insert @Return Values (@Token)
    SELECT @InString = STUFF(@InString, 1, PATINDEX(@Pattern, @InString),'')
    SELECT @Pos = PATINDEX(@Pattern, @InString)
    END
    --
    return
    --
    END

Options: ReplyQuote


Subject
Written By
Posted
Split String
March 27, 2006 10:10AM
April 12, 2007 04:29AM
December 17, 2007 09:46AM
February 20, 2008 12:29PM
January 14, 2009 02:22PM
w c
January 29, 2009 10:05PM
March 18, 2009 03:13AM
January 24, 2008 06:17PM
March 04, 2008 08:50AM
August 23, 2008 04:33PM
January 14, 2009 01:40PM
September 08, 2008 04:27AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.