MySQL: Query Real Values from Delimiter-Separated-String-IDs
September 15, 2008
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.
Related posts
Posted in Code Library, MySQL, Scripts
content rss
October 15th, 2008 at 11:03 pm
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
October 15th, 2008 at 11:35 pm
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
November 22nd, 2008 at 3:59 pm
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…….
November 22nd, 2008 at 6:11 pm
Hi Priya,
did you change the query that I stated in the code? can you show me the code?
thanks
November 22nd, 2008 at 6:31 pm
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 ;
November 26th, 2008 at 3:26 pm
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
February 22nd, 2009 at 2:54 am
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
February 22nd, 2009 at 2:58 am
Oops the thinghy ate my cookie!!!
… cus I used > and < 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;
February 25th, 2009 at 10:35 am
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
March 18th, 2009 at 11:24 am
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
March 18th, 2009 at 1:51 pm
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
March 18th, 2009 at 8:58 pm
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
March 18th, 2009 at 10:58 pm
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
March 18th, 2009 at 11:47 pm
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
March 18th, 2009 at 11:54 pm
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
March 19th, 2009 at 12:01 am
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
March 19th, 2009 at 12:04 am
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
March 19th, 2009 at 12:08 am
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;
March 19th, 2009 at 12:10 am
Hi Bradley,
edit … see comment below
=)
thanks
March 19th, 2009 at 12:12 am
oh… wait…
I think I missed something here…
try this…
SELECT ContentTitleName INTO temp_val FROM ContentTitle WHERE ContentTitleID=temp_str;
March 19th, 2009 at 12:19 am
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
March 19th, 2009 at 12:28 am
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 INTO temp_val FROM WHERE
=temp_str;
SELECT
….
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
March 19th, 2009 at 12:30 am
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
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 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 ‘,’ FROM out_str);
RETURN(out_str); – return
END $$
DELIMITER ;
March 19th, 2009 at 1:36 am
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
March 19th, 2009 at 8:12 am
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
March 19th, 2009 at 8:30 am
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
March 19th, 2009 at 8:40 am
Hi brad,
googling around, there is a function for that…
<cfset result= ListToArray(result, “,”)>
thanks
April 3rd, 2009 at 4:15 am
Cool but… why having comma separated values in the first place?
http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html
April 3rd, 2009 at 9:45 am
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…
June 15th, 2009 at 6:44 pm
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′,’-'));
June 16th, 2009 at 5:53 pm
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
June 18th, 2009 at 4:47 pm
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.
July 2nd, 2009 at 1:51 am
You should look at the function:
FIND_IN_SET
It could reduce the code needed.
Regards
July 2nd, 2009 at 1:10 pm
thanks for the tip!
I might check into that this weekend. =)
November 17th, 2009 at 6:13 am
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′)
November 17th, 2009 at 12:56 pm
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