Splitting the string in MySql

Hi.

This Article, A MySQL Tidbit: In-line CSV Parsing, I wrote should be helpful to you.  If nothing else, it has SQL to create a numbers utility table if you do not already have one.  If you have an unknown number of items in the comma separated list, most solutions to split these will probably involve a join to a numbers table that you can then use find all the instances of ',' for use in substring of other string function of that nature.

If my approach in the Article is not desired, please post back and we can explore some of the other alternatives.

HTH,

Kevin

lmc

can u give me the simple solution.. i don't need count....

Here is another approach involving numbers table that basically breaks apart each character and then correlates them back together based on index of the ',' closest to and after that character.

SELECT TRIM(GROUP_CONCAT(
               SUBSTRING(searchKeywords, n, 1) 
               ORDER BY n 
               SEPARATOR ''
            )) AS searchKeyword
FROM {Your_Table_Here} tbl
JOIN util.numbers nums
   ON n BETWEEN 1 AND CHAR_LENGTH(searchKeywords)
   AND SUBSTRING(searchKeywords, n, 1) <> ','
GROUP BY LOCATE(',', searchKeywords, n)
;

Select allOpen in new window

Hope that helps!

Oh, on the above, it works on one row of data "as-is".  For multiple rows of CSV data, you will need to group on something that makes the row unique.

SELECT TRIM(GROUP_CONCAT(
               SUBSTRING(searchKeywords, n, 1) 
               ORDER BY n 
               SEPARATOR ''
            )) AS searchKeyword
FROM {Your_Table_Here} tbl
JOIN util.numbers nums
   ON n BETWEEN 1 AND CHAR_LENGTH(searchKeywords)
   AND SUBSTRING(searchKeywords, n, 1) <> ','
GROUP BY {ID}, LOCATE(',', searchKeywords, n)
;

Select allOpen in new window

when i am trying to create the util.numbers...

i am getting the error message like this

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''util'.'numbers'(n)
select @rownum:=@rownum+1
from (
   select 0 union select' at line 1



and the table schema is


-- create database|schema for utility objects
create schema if not exists 'util';
-- drop any existing copies of numbers
drop table if exists 'util'.'numbers';
-- create numbers table structure (very basic)
create table 'util'.'numbers' (
  'n' int(11) not null,
  primary key ('n')
) engine=InnoDB default charset=latin1;
-- insert generated number sequence (e.g., 1-1,000,000)
insert into 'util'.'numbers'(n)
select @rownum:=@rownum+1
from (
   select 0 union select 1 union select 2 union select 3
   union select 4 union select 5 union select 6
   union select 7 union select 8 union select 9
) a, (
   select 0 union select 1 union select 2 union select 3
   union select 4 union select 5 union select 6
   union select 7 union select 8 union select 9
) b, (
   select 0 union select 1 union select 2 union select 3
   union select 4 union select 5 union select 6
   union select 7 union select 8 union select 9
) c, (
   select 0 union select 1 union select 2 union select 3
   union select 4 union select 5 union select 6
   union select 7 union select 8 union select 9
) d, (
   select 0 union select 1 union select 2 union select 3
   union select 4 union select 5 union select 6
   union select 7 union select 8 union select 9
) e, (
   select 0 union select 1 union select 2 union select 3
   union select 4 union select 5 union select 6
   union select 7 union select 8 union select 9
) f, (select @rownum:=0) r
;

You have single quotes where you should have ticks (`).  You need to fix the syntax.  That is why I directed you to the article, so you could simply select all on the code snippet and paste into MySQL versus having to retype manually.

no.. i have changed that .. can u check the above code.. which i was posted

>> no.. i have changed that .. can u check the above code.. which i was posted
Exactly.  That is the problem.  The ticks were not an error.  The code in the article was validated copy and paste ready code.  Changing ` to ' is not correct and is the issue; therefore, please fix the syntax back to what it was originally and run again.

Thanks... i have created the number table in my own db...

But i am running this snippet...

SELECT TRIM(GROUP_CONCAT(
               SUBSTRING(SearchKeywords, n, 1)
               ORDER BY n
               SEPARATOR ''
            )) AS SearchKeywords
FROM coursedetails tbl
JOIN numbers nums
   ON n BETWEEN 1 AND CHAR_LENGTH(SearchKeywords)
   AND SUBSTRING(SearchKeywords, n, 1) <> ','
GROUP BY {ID}, LOCATE(',', SearchKeywords, n)
;

I got the below Error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '}, LOCATE(',', SearchKeywords, n)' at line 10

what is {ID} ... here????

That is where you should put the column that uniquely identifies the original row like an ID column.  Did you try my first query?

yeah.. thanks a lot

SELECT TRIM(GROUP_CONCAT(
               SUBSTRING(searchKeywords, n, 1)
               ORDER BY n
               SEPARATOR ''
            )) AS searchKeyword
FROM coursedetails tbl
JOIN numbers nums
   ON n BETWEEN 1 AND CHAR_LENGTH(searchKeywords)
   AND SUBSTRING(searchKeywords, n, 1) <> ','
GROUP BY LOCATE(',', searchKeywords, n)
;

working fine........

But not giving the distinct values... can i use the distinct keyword to get those???

what the table contains more rows.... will it be a performance hit????

There will be a bit of performance hit since you have to explode each row by the number of characters in the string.  Therefore, the more rows and/or the longer the keyword strings, the more performance hit there will be.  You will need to use EXPLAIN on the two alternatives I gave you and see which has better performance in your particular case.  I would go for the XML approach personally.

SELECT EXTRACTVALUE(xml, '//c[$@rownum]/text()') AS `searchKeyword`
FROM util.numbers AS nums
INNER JOIN (
   SELECT -- include other columns here if needed in final select
      CONCAT('<r><c>', REPLACE(searchKeywords, ', ', '</c><c>'), '</c></r>') AS xml
   FROM {Your_Table_Here}
) AS tbl ON nums.n BETWEEN 1 AND EXTRACTVALUE(xml, 'count(//c)')
   AND @rownum:=n
;

when i am trying to execute the following code.. it is giving

EXTRACTVALUE doesn't exist.. is it a function???

What version of MySQL are you running?

how to check the version from command line??

version is  5.0.22-community-nt

can you pls post me the Distinct results for this Query

SELECT TRIM(GROUP_CONCAT(
               SUBSTRING(searchKeywords, n, 1)
               ORDER BY n
               SEPARATOR ''
            )) AS searchKeywords
FROM coursedetails tbl
JOIN numbers nums
   ON n BETWEEN 1 AND CHAR_LENGTH(searchKeywords)
   AND SUBSTRING(searchKeywords, n, 1) <> ','
GROUP BY LOCATE(',', searchKeywords, n)
;

>> 5.0.22-community-nt
Okay, yes, the XML approach won't work with that version.  For the other version, just remember if you have more than one row then you need to use an ID or the original searchKeywords value to ensure you put characters back together appropriately.  DISTINCT keyword should be fine.

can you plzz reform the query for distinct ?

Thanks a lot..... working fine