MySQL: Query Real Values from Delimiter-Separated-String-IDs

I had a problem in using a string-comma-separated-value returned from a query in an “IN” statement. I used the IDs from one table, concat them into a comma separated value and insert them into another table. Baaaad idea… Now when I query that value, I can’t use it directly into an “IN” statement to retrieve their real values since its a string being returned from the query, the “IN” statement will not compare all the values inside as a set, but it will compare it as a string.

e.g.
SELECT value FROM my_table WHERE my_id IN (‘1, 2, 3’) is NOT equivalent to SELECT value FROM my_table WHERE my_id IN (‘1’, ‘2’, ‘3’)

So, if you have a table containing values 1 to 3, the first query will return only 1 while the second query will return all values; 1, 2 and 3.

I Googled around and found out that MySQL does not have a native equivalent of PHP’s explode() function. Crap… I had to do it the hard war and create a MySQL stored function to ‘explode’ the values from its delimiter, query the right value from the other table using the exploded IDs, concat them back together and return them as a string.

Below is the function I was able to patch together from different codes I found in the MySQL forum. I added comments below as to make things clearer. I named the function splitAndTranslate since that’s what I was really trying to implement. You can make up your own modifications and function name.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
DELIMITER //
DROP FUNCTION IF EXISTS `splitAndTranslate` //
CREATE FUNCTION splitAndTranslate(str TEXT, delim VARCHAR(124))
RETURNS TEXT
DETERMINISTIC
BEGIN
	DECLARE i INT DEFAULT 0;	-- total number of delimiters
	DECLARE ctr INT DEFAULT 0;	-- counter for the loop
	DECLARE str_len INT;		-- string length,self explanatory
	DECLARE out_str text DEFAULT '';	-- return string holder
	DECLARE temp_str text DEFAULT '';	-- temporary string holder
  	DECLARE temp_val VARCHAR(255) DEFAULT '';	-- temporary string holder for query
 
	-- get length
	SET str_len=LENGTH(str);	
 
	SET i = (LENGTH(str)-LENGTH(REPLACE(str, delim, '')))/LENGTH(delim) + 1;	
		-- get total number delimeters and add 1
		-- add 1 since total separated values are 1 more than the number of delimiters
 
	-- start of while loop
	WHILE(ctr<i) DO
		-- add 1 to the counter, which will also be used to get the value of the string
		SET ctr=ctr+1; 
 
		-- get value separated by delimiter using ctr as the index
		SET temp_str = REPLACE(SUBSTRING(SUBSTRING_INDEX(str, delim, ctr), LENGTH(SUBSTRING_INDEX(str, delim,ctr - 1)) + 1), delim, '');
 
		-- query real value and insert into temporary value holder, temp_str contains the exploded ID    		
		SELECT <real_value_column> INTO temp_val FROM <my_table> WHERE <table_id>=temp_str;
 
		-- concat real value into output string separated by delimiter
    		SET out_str=CONCAT(out_str, temp_val, ',');
	END WHILE;
	-- end of while loop
 
	-- trim delimiter from end of string
	SET out_str=TRIM(TRAILING delim FROM out_str);
	RETURN(out_str);	-- return 
 
END//

After creating the stored function, you can now use it normally like any MySQL function inside a query. So what I now do with the new function is…

SELECT splitAndTranslate( g.comma_separated_ids ) real_values FROM my_group_table g;

Thanks to Chris Stubben in the MySQL Forum, I used and modified his code to fit my need.

50 Replies to “MySQL: Query Real Values from Delimiter-Separated-String-IDs”

  1. Hello,

    this function is just what I desperately searched.
    One downside is I can’t get it to work properly πŸ™

    I always get the failure “#1146 – Table ‘xxx.my_values_table’ doesn’t exist “.
    Maybe you can help me on this?

    greetings
    Andreas

  2. hi andreas,

    you might want to check line 29, that’s my personal query for the real values that the comma-separated-values supposed to be…

    so replace line 29…

    SELECT real_value INTO temp_val FROM my_values_table WHERE value_id=temp_str;

    with your own query

    SELECT [your_value_column] INTO temp_val FROM [your_table] WHERE [id_from_your_table]=temp_str;

    tell me how it goes… πŸ™‚

    thanks

  3. Hi ,
    when i used this stored function its giving me an error stating: the used select statements have a different number of columns can u plz help me out with dis…….

  4. Thanks for ur reply..
    yes i have changed the query ie i have put my table name
    DELIMITER //
    DROP FUNCTION IF EXISTS `splitAndTranslate` //
    CREATE FUNCTION splitAndTranslate(str TEXT, delim VARCHAR(124))
    RETURNS TEXT
    DETERMINISTIC
    BEGIN
    DECLARE i INT DEFAULT 0; — total number of delimiters
    DECLARE ctr INT DEFAULT 0; — counter for the loop
    DECLARE str_len INT; — string length,self explanatory
    DECLARE out_str text DEFAULT ”; — return string holder
    DECLARE temp_str text DEFAULT ”; — temporary string holder
    DECLARE temp_val VARCHAR(255) DEFAULT ”; — temporary string holder for query

    — get length
    SET str_len=LENGTH(str);

    SET i = (LENGTH(str)-LENGTH(REPLACE(str, delim, ”)))/LENGTH(delim) + 1;
    — get total number delimeters and add 1
    — add 1 since total separated values are 1 more than the number of delimiters

    — start of while loop
    WHILE(ctr<i) DO
    — add 1 to the counter, which will also be used to get the value of the string
    SET ctr=ctr+1;

    — get value separated by delimiter using ctr as the index
    SET temp_str = REPLACE(SUBSTRING(SUBSTRING_INDEX(str, delim, ctr), LENGTH(SUBSTRING_INDEX(str, delim,ctr – 1)) + 1), delim, ”);

    — query real value and insert into temporary value holder, temp_str contains the exploded ID
    SELECT * INTO temp_val FROM emp WHERE empid=temp_str;

    — concat real value into output string separated by delimiter
    SET out_str=CONCAT(out_str, temp_val, ‘,’);
    END WHILE;
    — end of while loop

    — trim delimiter from end of string
    SET out_str=TRIM(TRAILING delim FROM out_str);
    RETURN(out_str); — return

    END//

    delimiter ;

  5. hi priya,

    sorry for the late response…

    i see your problem….

    your query

    SELECT * INTO temp_val FROM emp WHERE empid=temp_str;

    should not use asterisk (*) in the select statement, it should be the employee name or the info you want retrieved. =)

    so in a sense it should look something like…

    SELECT employee_name INTO temp_val FROM emp WHERE empid=temp_str;

    tell me how it went…

    thanks

  6. Hi!

    This was very helpful. Thank you! Below is the version I ended up using to iterate to a collection of comma-separated values where ” just a marker (something you would never find in the input string, either because you created it yourself or because it has been validated). The key, as in the original blog is to play with +/- values in substring_index function.


    set _term = '';
    if _csv != '' then
    set _csv = concat(_csv,',',_term);
    set i = 1;
    loop_csv: loop
    set _item = substring_index(substring_index(_csv,',',i),',',-1);
    if _item = _term then leave loop_csv; end if;
    set i = i + 1;
    select concat('do things with ',item);
    end loop;
    end if;

    Thanks!!!

    Ed

  7. Oops the thinghy ate my cookie!!!

    … cus I used > and &lt in the example. Here it is again with ‘-EOL-‘ instead

    set _term = '-EOL-';
    if _csv != '' then
    set _csv = concat(_csv,',',_term);
    set i = 1;
    loop_csv: loop
    set _item = substring_index(substring_index(_csv,',',i),',',-1);
    if _item = _term then leave loop_csv; end if;
    set i = i + 1;
    select concat('do things with ',item);
    end loop;
    end if;

  8. Hi Ed,

    glad it helped you, been bugging me a lot few months back I couldn’t do the php’s version of “explode” in mysql LOL

  9. Hello,
    I’m able to compile the code to create the function with no problem, however receive the following error when attempting to run the test query you supplied. The query I’m running is as follows:
    SELECT splitAndTranslate(g.contenttitlelist) real_values FROM submittedlesson g;
    The error is as follows:
    Incorrect number of arguments for FUNCTION lessonplansdb.splitAndTranslate; expected 2, got 1
    I am trying to split the id values that are concatenated within the submittedlesson table, within the ContentTitleList field. The main id field of the submittedlesson table is the submittedlessonid field. The code I was using is as follows:
    DELIMITER $$

    DROP FUNCTION IF EXISTS `splitAndTranslate` $$
    CREATE DEFINER=`graphsol`@`172.16.1.%` FUNCTION `splitAndTranslate`(str TEXT, delim VARCHAR(124)) RETURNS text CHARSET latin1
    DETERMINISTIC
    BEGIN
    DECLARE i INT DEFAULT 0; — total number of delimiters
    DECLARE ctr INT DEFAULT 0; — counter for the loop
    DECLARE str_len INT; — string length,self explanatory
    DECLARE out_str text DEFAULT ”; — return string holder
    DECLARE temp_str text DEFAULT ”; — temporary string holder
    DECLARE temp_val VARCHAR(255) DEFAULT ”; — temporary string holder for query

    — get length
    SET str_len=LENGTH(str);

    SET i = (LENGTH(str)-LENGTH(REPLACE(str, delim, ”)))/LENGTH(delim) + 1;
    — get total number delimeters and add 1
    — add 1 since total separated values are 1 more than the number of delimiters

    — start of while loop
    WHILE(ctr<i) DO
    — add 1 to the counter, which will also be used to get the value of the string
    SET ctr=ctr+1;

    — get value separated by delimiter using ctr as the index
    SET temp_str = REPLACE(SUBSTRING(SUBSTRING_INDEX(str, delim, ctr), LENGTH(SUBSTRING_INDEX(str, delim,ctr – 1)) + 1), delim, ”);

    — query real value and insert into temporary value holder, temp_str contains the exploded ID
    SELECT contenttitlelist INTO temp_val FROM submittedlesson WHERE submittedlessonid=temp_str;

    — concat real value into output string separated by delimiter
    SET out_str=CONCAT(out_str, temp_val, ‘,’);
    END WHILE;
    — end of while loop

    — trim delimiter from end of string
    SET out_str=TRIM(TRAILING delim FROM out_str);
    RETURN(out_str); — return

    END $$

    DELIMITER ;

    Any assistance would be MUCH appreciated. Thanks.

    Bradley

  10. Hi Bradley,

    Checking your code …
    SELECT splitAndTranslate(g.contenttitlelist) real_values FROM submittedlesson g;

    you left ouf the delimeter part in which the list used to separate your data.

    So it should look like…

    SELECT splitAndTranslate(g.contenttitlelist, ‘[delimeter]’) real_values FROM submittedlesson g;

    or…

    if you manually added the delimeter in the code body, you can remove the delim VARCHAR[124] from this line…

    FUNCTION `splitAndTranslate`(str TEXT, delim VARCHAR(124)) RETURNS text

    to look like this…

    FUNCTION `splitAndTranslate`(str TEXT) RETURNS tex

    hope this helps

    thanks

  11. Hello again,
    THANKS SO MUCH for responding. I have tried the following query to return the desired data:
    SELECT splitAndTranslate(g.contenttitlelist,’,’) real_values FROM submittedlesson g where submittedlessonid = 64;
    I get the following results:
    No data – zero rows fetched, selected, or processed
    No data – zero rows fetched, selected, or processed
    No data – zero rows fetched, selected, or processed

    The value in the contenttitlelist field in the submittedlesson table is 1,7,4 for submittedlessonid =64. Any ideas why this might be happening before I attempt to edit the code in the function? Thanks again.

    Bradley

  12. Hi Bradley,

    question.. checking this line of code…

    SELECT contenttitlelist INTO temp_val FROM submittedlesson WHERE submittedlessonid=temp_str;

    and

    your mysql query…

    SELECT splitAndTranslate(g.contenttitlelist,’,’) real_values FROM submittedlesson g where submittedlessonid = 64;

    why did you select the contenttitlelist again as the result for the query?

    shouldn’t you be querying something like the actual title?

    SELECT title INTO temp_val FROM submittedlesson WHERE submittedlessonid=temp_str;

    thanks

  13. Some Added info,
    I’ve changed the function code provided to read the following:
    DELIMITER $$

    DROP FUNCTION IF EXISTS `lessonplansdb`.`splitAndTranslate` $$
    CREATE DEFINER=`graphsol`@`172.16.1.%` FUNCTION `splitAndTranslate`(str TEXT) RETURNS text CHARSET latin1
    DETERMINISTIC
    BEGIN
    DECLARE i INT DEFAULT 0; — total number of delimiters
    DECLARE ctr INT DEFAULT 0; — counter for the loop
    DECLARE str_len INT; — string length,self explanatory
    DECLARE out_str text DEFAULT ”; — return string holder
    DECLARE temp_str text DEFAULT ”; — temporary string holder
    DECLARE temp_val VARCHAR(255) DEFAULT ”; — temporary string holder for query

    — get length
    SET str_len=LENGTH(str);

    SET i = (LENGTH(str)-LENGTH(REPLACE(str, ‘,’, ”)))/LENGTH(‘,’) + 1;
    — get total number delimeters and add 1
    — add 1 since total separated values are 1 more than the number of delimiters

    — start of while loop
    WHILE(ctr<i) DO
    — add 1 to the counter, which will also be used to get the value of the string
    SET ctr=ctr+1;

    — get value separated by delimiter using ctr as the index
    SET temp_str = REPLACE(SUBSTRING(SUBSTRING_INDEX(str, ‘,’, ctr), LENGTH(SUBSTRING_INDEX(str, ‘,’,ctr – 1)) + 1), ‘,’, ”);

    — query real value and insert into temporary value holder, temp_str contains the exploded ID
    SELECT contenttitlelist INTO temp_val FROM submittedlesson WHERE submittedlessonid=temp_str;

    — concat real value into output string separated by delimiter
    SET out_str=CONCAT(out_str, temp_val, ‘,’);
    END WHILE;
    — end of while loop

    — trim delimiter from end of string
    SET out_str=TRIM(TRAILING ‘,’ FROM out_str);
    RETURN(out_str); — return

    END $$

    DELIMITER ;

    Query below resulted in the same warning/error menioned above:
    SELECT splitAndTranslate(g.contenttitlelist) real_values FROM submittedlesson g;

    I do see that it returns “real_values” more the query mentioned in my previous post and for this one, however there is a empty string in the resultset. The error number is 1329. Just as a side note I checked the length of my contenttitlelist field within submittedlesson as it reported 5 characters as expected. Also the contenttitlelist field is a varchar(500) if that matters. I am at a total loss except that I will never code a concatenated string of ids again, but for this project WAY to late for that. Hope this brings some more clarification as to what is wrong with how the function is being used in my case and THANKS AGAIN!!!!

    Bradley

  14. HI Bradley,

    Can you check the one in bold if this correct?

    – query real value and insert into temporary value holder, temp_str contains the exploded ID
    SELECT contenttitlelist INTO temp_val FROM submittedlesson WHERE submittedlessonid=temp_str;

    why did you retrieve the contenttitlelist from submittedlesson table when it is the one you passed in the argument in the first place?

    can you show me the table structure?

    thanks

  15. Hello,
    To answer your question, and to better explain my layout of the tables, I’ve got the contenttitle table that has a contenttitleid and contenttitlename fields. I have concatenated the contenttitleids needed as a comma delimited string within the contenttitlelist field in the submittedlesson table.
    I want to create a select statement that will pull the contenttitleids from the contenttitlelist field in the submittedlesson table within an in() function, for example:
    SELECT contenttitle.contenttitlename FROM contenttitle, submittedlesson WHERE contenttitle.contenttitleid IN(submittedlesson.Contenttitlelist);
    It is definately getting clear that the problem is within my “SELECT contenttitlelist INTO temp_val FROM submittedlesson WHERE submittedlessonid=temp_str;” statement, but my mind feels like mush as to what it should be. Hope this sheds some light on the problem.

    Bradley

  16. Hi,
    yup I think so too… try changing contenttitlelist to the actual content title.

    can you post the table structure? not the data, just how the table is made.

    thanks

  17. Hello,
    Below is the structure for the contenttitle table:
    DROP TABLE IF EXISTS `lessonplansdb`.`contenttitle`;
    CREATE TABLE `lessonplansdb`.`contenttitle` (
    `ContentTitleID` int(10) unsigned NOT NULL auto_increment,
    `ContentTitleName` varchar(500) default NULL,
    `Content_Title_SortOrder` int(10) unsigned default NULL,
    PRIMARY KEY (`ContentTitleID`)
    ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=latin1;

    With the structures mentioned above, could you provide me with exactly how the following statement should read:
    β€œSELECT contenttitlelist INTO temp_val FROM submittedlesson WHERE submittedlessonid=temp_str;”

    Thanks.

    Bradley

    Below is the structure for the submittedlesson table:
    DROP TABLE IF EXISTS `lessonplansdb`.`submittedlesson`;
    CREATE TABLE `lessonplansdb`.`submittedlesson` (
    `SubmittedLessonID` int(10) unsigned NOT NULL auto_increment,
    `RegistrationID` int(10) unsigned default NULL,
    `LessonTitle` varchar(45) default NULL,
    `SchoolID` int(10) unsigned default NULL,
    `TeachingStrategies` varchar(250) default NULL,
    `PartnerInvolvement` varchar(250) default NULL,
    `FocusQuestions` varchar(250) default NULL,
    `StudentOutcomes` varchar(250) default NULL,
    `TimeFrame` varchar(45) default NULL,
    `StudentPractice` varchar(250) default NULL,
    `Assessment` varchar(250) default NULL,
    `ExtentionActivities` varchar(250) default NULL,
    `TeacherTips` varchar(250) default NULL,
    `DateCreated` datetime default NULL,
    `Status` varchar(150) default NULL,
    `ContentTitleList` varchar(500) default NULL,
    `ThemeList` varchar(500) default NULL,
    `StateStandardsList` varchar(500) default NULL,
    PRIMARY KEY (`SubmittedLessonID`)
    ) ENGINE=InnoDB AUTO_INCREMENT=72 DEFAULT CHARSET=latin1;

  18. oh… wait…

    I think I missed something here…

    try this…

    SELECT ContentTitleName INTO temp_val FROM ContentTitle WHERE ContentTitleID=temp_str;

  19. Hello,
    Updated the function and ran the following query with the same errant results:
    SELECT splitAndTranslate(g.contenttitlelist) real_values FROM submittedlesson g;

    I am unclear as to how the change in your last post can assist in pulling the ids as individual strings in my needed query:
    SELECT contenttitle.contenttitlename FROM contenttitle, submittedlesson WHERE contenttitle.contenttitleid IN(submittedlesson.contenttitlelist);

    Bradley

  20. Hi Bradley,

    the only thing in my code that you need to change is this part

    — query real value and insert into temporary value holder, temp_str contains the exploded ID
    SELECT INTO temp_val FROM WHERE =temp_str;

    ….

    the SELECT statement there will query the actual data from the IDs you passed.
    So checking your comments above, I understood that you passed the IDs of the ContentTitle and want the ContentTitleName from the table. So modifying the SELECT statement I posted above to your requirement would result in a SELECT statement…

    SELECT c.ContentTitleName INTO temp_val FROM ContentTitle c WHERE c.ContentTitleID=temp_str;

    hope this helps

    thanks

  21. can you try this?? …..

    DELIMITER $$

    DROP FUNCTION IF EXISTS `lessonplansdb`.`splitAndTranslate` $$
    CREATE DEFINER=`graphsol`@`172.16.1.%` FUNCTION `splitAndTranslate`(str TEXT) RETURNS text CHARSET latin1
    DETERMINISTIC
    BEGIN
    DECLARE i INT DEFAULT 0; – total number of delimiters
    DECLARE ctr INT DEFAULT 0; – counter for the loop
    DECLARE str_len INT; – string length,self explanatory
    DECLARE out_str text DEFAULT ”; – return string holder
    DECLARE temp_str text DEFAULT ”; – temporary string holder
    DECLARE temp_val VARCHAR(255) DEFAULT ”; – temporary string holder for query

    – get length
    SET str_len=LENGTH(str);

    SET i = (LENGTH(str)-LENGTH(REPLACE(str, β€˜,’, ”)))/LENGTH(’,’) + 1;
    – get total number delimeters and add 1
    – add 1 since total separated values are 1 more than the number of delimiters

    – start of while loop
    WHILE(ctr

  22. Hello again,
    I think there may be an error with the copy provided above in the line:
    WHILE(ctr – add 1 to the counter, which will also be used to get the value of ….

    In any case I copied the line of code you provided and kept the passing of the delimiter to the function as previously stated:
    DELIMITER $$

    DROP FUNCTION IF EXISTS `lessonplansdb`.`splitAndTranslate` $$
    CREATE DEFINER=`graphsol`@`172.16.1.%` FUNCTION `splitAndTranslate`(str TEXT, delim VARCHAR(124)) RETURNS text CHARSET latin1
    DETERMINISTIC
    BEGIN
    DECLARE i INT DEFAULT 0; — total number of delimiters
    DECLARE ctr INT DEFAULT 0; — counter for the loop
    DECLARE str_len INT; — string length,self explanatory
    DECLARE out_str text DEFAULT ”; — return string holder
    DECLARE temp_str text DEFAULT ”; — temporary string holder
    DECLARE temp_val VARCHAR(255) DEFAULT ”; — temporary string holder for query

    — get length
    SET str_len=LENGTH(str);

    SET i = (LENGTH(str)-LENGTH(REPLACE(str, delim, ”)))/LENGTH(delim) + 1;
    — get total number delimeters and add 1
    — add 1 since total separated values are 1 more than the number of delimiters

    — start of while loop
    WHILE(ctr<i) DO
    — add 1 to the counter, which will also be used to get the value of the string
    SET ctr=ctr+1;

    — get value separated by delimiter using ctr as the index
    SET temp_str = REPLACE(SUBSTRING(SUBSTRING_INDEX(str, delim, ctr), LENGTH(SUBSTRING_INDEX(str, delim,ctr – 1)) + 1), delim, ”);

    — query real value and insert into temporary value holder, temp_str contains the exploded ID
    SELECT c.ContentTitleName INTO temp_val FROM ContentTitle c WHERE c.ContentTitleID=temp_str;

    — concat real value into output string separated by delimiter
    SET out_str=CONCAT(out_str, temp_val, ‘,’);
    END WHILE;
    — end of while loop

    — trim delimiter from end of string
    SET out_str=TRIM(TRAILING delim FROM out_str);
    RETURN(out_str); — return

    END $$

    Using the function as it is stated above does pull the contenttilenames, in a comma delimited list if I run the query as follows, but that is not what is needed:
    select splitAndTranslate(submittedlesson.contenttitlelist, ‘,’) contenttitle FROM submittedlesson;
    example:
    ‘Age of Innovation and Industry,World War II,Civil Rights’

    The ultimate result is to pull each of the contenttitlenames in individual row using a query as such below but currently results in the same error mentioned above:
    select contenttitle.contenttitlename FROM submittedlesson, contenttitle WHERE contenttitle.contenttitlename in(splitAndTranslate(submittedlesson.contenttitlelist, ‘,’));
    Age of Innovation and Industry
    World War II
    Civil Rights

    I hope you don’t give up on me yet as I think we’re close, possibly:)

    Bradley

  23. Hi brad,

    At least were close already… One thing to note.. this is a function not a store procedure… so it can only return ONE result at a time. What you wanted can be done using stored procedures, which will return a resultset and in different rows as you wanted.

    What I can suggest is to use the code above and manually ‘explode’ the ContentTitleName using PHP or any scripting language that you using. If thats what you are intending.

    thanks

  24. Hello,
    Thanks again for all your help and will definately look into the stored procedure, or in my case a ColdFusion explode type function.

    Bradley

  25. Hi brad,

    googling around, there is a function for that…

    <cfset result= ListToArray(result, “,”)>

    thanks

  26. I was contemplating on having a many-to-many relationships though I thought having this would be easier on my end _and_ I thought mysql would have an ‘explode’ function like php… BIG mistake…it didn’t LOL, so later on this bit me in the butt…

  27. You showed in your first example a WHERE IN clause, how do you use this?
    my query returns zero rows:

    SELECT PhysicalZone, Description FROM csidzone WHERE PhysicalZone IN (splitAndTranslate(‘001-002-003′,’-‘));

  28. hi Jordan,

    you cannot use the returned values as a resultset, its still a string but instead of the numbers separated by “-“, is now converted to your PhysicalZone names.

    so example:

    having ’001-002-003β€² as your input…

    splitAndTranslate(’001-002-003β€²,’-‘)

    will return it into

    ’PhysicalZone1-PhysicalZone2-PhysicalZone3β€²

    so its still a string.

    What you want can be done via mysql Procedure, currently mysql Function does not do this.

    Another way for you I can suggest is to get the PhysicalZone by using the function, split it by using your scripting language, (ex. php has ‘explode’ function) then populate your IN statement, then execute the query.

    thanks

  29. Thanks Chaoz. I’m working with a MySQL procedure now that creates a temp table and it seems to be working as much as I should expect. The real problem here is I don’t have a scripting language. It’s a project at work, and we have fairly substandard systems; it’s been a chore and a half just getting this to work with Excel and MS Query. I think I’ll have to go with a full php implementation, but I’d rather not spend that much time on a company project (because I’m a security guard for now). Thank you again.

  30. The following code works for me:

    SELECT value FROM my_table WHERE FIND_IN_SET(my_id, β€²1, 2, 3β€²);

    which is similar to the following code:

    SELECT value FROM my_table WHERE my_id IN (’1β€², β€²2β€², β€²3β€²)

  31. yup that will do if not used in a query, but what I needed was the ‘1, 2, 3’ to be used as part of a query πŸ™‚

  32. Hi
    The

    SELECT value FROM my_table WHERE FIND_IN_SET(my_id, β€²1, 2, 3β€²);

    is not the same as

    SELECT value FROM my_table WHERE my_id IN (’1β€², β€²2β€², β€²3β€²)

    In first quiry FULL TABLE SCAN is required. This is not a solution for big tables

  33. Hi,

    Can you explain further with FULL TABLE SCAN?
    This is the first time I encountered this.

    Thanks

  34. Hi!

    Thanks very much for your code, saved me a lot of work! I’ve customised it slightly for my requirements and it works perfectly!

  35. Hey there! This is my first visit to your blog!
    We are a collection of volunteers and starting a new initiative in
    a community in the same niche. Your blog provided us valuable information
    to work on. You have done a extraordinary job!

  36. Hi,

    I have a column in my table with Multiple comma Seprated Values Stored in It, Now i want to Compare the single Value with Multiple values…

    Like,
    *************
    $query_s=”select * from TableName1 where ColumnName1='”.$Var1.”‘”;
    $exe_s=mysql_query($query_s);

    while($res_s1=mysql_fetch_array($exe_s))
    {
    $a=explode(“,”,$res_s1[‘ColumnName2’]);
    $count=count($a);

    for($i=0;$i<=$count;$i++)
    {
    if($a[$i] == $Var2)
    {
    $sql="SELECT * FROM TableName1 WHERE ColumnName2(Containing Multiple Values with Comma Seprated)=".$Var2."";
    $result=mysql_query($sql);
    }
    ********

    This Code don't Print ROWS having Multiple Values in Column2….

    HELP ME……..

    Thanx In Advance…. πŸ™‚

  37. Hello,
    I am having problem to search data from two tables where both fields have comma separated values in different order.

    For instance, I have two tables:
    1. Users
    id gender ethnicity
    1 male asian,american,african
    2 female asian,african,american
    3 female any

    2. Roles
    id ethnicity
    25 american,asian,african
    102 african,american,asian
    451 any
    402 any,pecific islander

    Now, I want to fetch data from both the tables on the basis of “ethnicity” field. Condition is, at least one value should match from both the fields. Here after comparing both the tables there may be multiple records we may have but I need one record for same user and same role. If same user will have same role and will have multiple records then we need one of all records.

    I did R&D but didn’t find anything for this case. So please help me out for this and give me your best solution for this as soon as possible. I hope I will get a solutions from your side for sure.

    Thanks in Advance.. πŸ™‚

  38. Hi Dipak,

    I don’t think you need the WHILE loop there…

    as for checking if the variable is in the multiple comma separated values, you can keep it simple… what you can do is use LIKE

    something like… SELECT * FROM Table1 WHERE columnName2 LIKE ‘%$var2%’

Leave a Reply

Your email address will not be published. Required fields are marked *