• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 491
  • Last Modified:

Splitting the string in MySql

Hi,

I have a table with the column searchKeywords...
In searchKeywords column the data format is like this "C#,.NET,PHP"  and ofcourse no.of rows are there
I want to split these strings with "," and need to get the distinct languages list from all the rows.


Thanks,
Ajay
0
ajayvegesna02
Asked:
ajayvegesna02
  • 12
  • 11
2 Solutions
 
Kevin CrossChief Technology OfficerCommented:
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
0
 
ajayvegesna02Author Commented:
lmc
0
 
ajayvegesna02Author Commented:
can u give me the simple solution.. i don't need count....
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
Kevin CrossChief Technology OfficerCommented:
Sure thing.  You will of course need to read through the Article for understanding of how this works, but more importantly ensure to generate numbers table using the SQL here:
http://www.experts-exchange.com/Database/MySQL/A_3573-A-MySQL-Tidbit-Quick-Numbers-Table-Generation.html

Once you have util.numbers, then run the following code:
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
;

Open in new window


Kevin
0
 
Kevin CrossChief Technology OfficerCommented:
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)
;

Open in new window


Hope that helps!
0
 
Kevin CrossChief Technology OfficerCommented:
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)
;

Open in new window

0
 
ajayvegesna02Author Commented:
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
;
0
 
Kevin CrossChief Technology OfficerCommented:
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.
0
 
ajayvegesna02Author Commented:
no.. i have changed that .. can u check the above code.. which i was posted

0
 
Kevin CrossChief Technology OfficerCommented:
>> 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.
0
 
ajayvegesna02Author Commented:
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????


0
 
Kevin CrossChief Technology OfficerCommented:
That is where you should put the column that uniquely identifies the original row like an ID column.  Did you try my first query?
0
 
ajayvegesna02Author Commented:
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????

0
 
Kevin CrossChief Technology OfficerCommented:
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.
0
 
ajayvegesna02Author Commented:
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???
0
 
Kevin CrossChief Technology OfficerCommented:
What version of MySQL are you running?
0
 
ajayvegesna02Author Commented:
how to check the version from command line??
0
 
ajayvegesna02Author Commented:
version is  5.0.22-community-nt
0
 
ajayvegesna02Author Commented:
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)
;
0
 
Kevin CrossChief Technology OfficerCommented:
>> 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.
0
 
ajayvegesna02Author Commented:
can you plzz reform the query for distinct ?
0
 
Kevin CrossChief Technology OfficerCommented:
e.g.,
SELECT DISTINCT searchKeywords
FROM (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)) derived
;
0
 
ajayvegesna02Author Commented:
Thanks a lot..... working fine
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 12
  • 11
Tackle projects and never again get stuck behind a technical roadblock.
Join Now