Mysql doesn't support table valued
function like sql server, Oracle etc. Sometimes we need to convert a comma delimited
string to rows of a column. In Mysql we can do it in different way:
Mysql
script to convert a comma delimited string to rows of a column:
SET @strCsv = '1,5,70,2,18,cow,mow';
SET
@StrDelimiter = ',';
SELECT vcItem
FROM(
SELECT
@vcItem:= SUBSTRING_INDEX(@strCsv,@StrDelimiter,1) AS vcItem,
@strCsv := SUBSTRING(@strCsv,CHAR_LENGTH(@vcItem) + 2),
@Count := @Count + 1
FROM information_schema.COLLATIONS,(SELECT @Count:= 1,@vcItem:= '') AS C
)tblSplit
WHERE vcItem <> '';
Sample output:
vcItem
|
1
|
5
|
70
|
2
|
18
|
cow
|
mow
|
No comments:
Post a Comment