Jan 7, 2013

Table valued function in Mysql

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