Split String
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
Subject
Written By
Posted
Split String
March 27, 2006 10:10AM
December 17, 2007 09:46AM
February 20, 2008 12:29PM
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.