-1

I have status field on my db with this value 

172:6,173:6,174:7

Where 172,173, 174 are my id and 6,7 are my status separated by : in my string.

Now i want to write down a query in mysql to get id whose status is 6, How can i explode my string to get the ids.

my first table is education details table having Field

id   lead_ref  status
1      R-1      172:6,173:6,174:7

My next table id education target

id    target_course name
172      A
173      B
174      C

Now i want to join these two table and get those ids whose status is 6

nitin
  • 89
  • 10
  • assuming that you want `172` and `173` as your results given the above example: I'd parse out the string, shove it all into a temp table with ID and STATUS columns, and then run your query against that. – Russell Uhl Jul 10 '14 at 12:32
  • 2
    If there is any chance to normalize the database, you should do so. Selecting with string comparison and placeholders is the worst case to retrieve data from a DBMS. Create a separate table for the relation and query that one. – feeela Jul 10 '14 at 12:34
  • I have join of many tables and i just want the status of my id should be six, By normalizing my database to temp table and then using is bit complex process. – nitin Jul 10 '14 at 12:38
  • 1
    No, what you are doing right now is a complex task and when dealing with millions of entries in a table, it will run very slow. Go the right way, not the easy one – it will save you a lot of trouble in the future… – feeela Jul 10 '14 at 12:39
  • For this i have to change the db structure and i dont have time to make so much changes Is any way to do so can can processed to next phase of my project – nitin Jul 10 '14 at 12:42
  • Can you show some more sample data and desired results? Your question is not clear on what a row looks like and what `id` refers to. – Gordon Linoff Jul 10 '14 at 12:48

2 Answers2

0

There you go:

SELECT `status`
FROM `table`
WHERE `status` LIKE `%:6,%`  -- if there is a matching status before a comma
   OR `status` LIKE `%:6`;   -- if there is matching status as last item

There is no easy possibility in MySQL to split/explode a string. See Can you split/explode a field in a MySQL query?

Community
  • 1
  • 1
feeela
  • 29,399
  • 7
  • 59
  • 71
  • This is the version of my answer you should use if you have indeed a comma separated list in your entries and not separate entries. – tholu Jul 10 '14 at 12:45
0

It is possible, but the method to do it is better at persuading you to redesign your database than it is as something to use in a live environment.

You can generate a range of numbers by unioning constants together. It you union the numbers 0 to 9 and cross join that against itself you can generate the number 0 to 99 (and another cross join to give you 0 to 999, etc). You can use this with SUBSTRING_INDEX to split up the string based on commas.

As this will provide duplicates (using substring_index in this way will bring back the last one as many times as it takes to increase the number returned to the max number it copes with) you can then use DISTINCT.

SELECT DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(some_field, ',', 1 + units.i + tens.i * 10), ',', -1), ':', 1) AS id
FROM some_table
CROSS JOIN (SELECT 0 i 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) units
CROSS JOIN (SELECT 0 i 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) tens
WHERE SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(some_field, ',', 1 + units.i + tens.i * 10), ',', -1), ':', -1)  = 6

If you wanted the duplicates returned the right number of times you need to check the number of comma separated values (compare the length of the field with the length of the field with all the commas replaced by nothing to get the number of commas):-

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(some_field, ',', 1 + units.i + tens.i * 10), ',', -1), ':', 1) AS id
FROM some_table
CROSS JOIN (SELECT 0 i 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) units
CROSS JOIN (SELECT 0 i 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) tens
WHERE SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(some_field, ',', 1 + units.i + tens.i * 10), ',', -1), ':', -1)  = 6
AND (units.i + tens.i * 10) <= (LENGTH(some_field) - LENGTH(REPLACE(some_field, ',','')))

Note that both of these only cope with a max of 100 comma separated values. Easy to expand them to cope with more but that will also slow the queries down.

EDIT - to get the target course name for the status with the demo date you have now posted above:-

SELECT education_details.id, education.target_course_name
FROM education_details
CROSS JOIN (SELECT 0 i 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) units
CROSS JOIN (SELECT 0 i 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) tens
INNER JOIN education ON SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(status, ',', 1 + units.i + tens.i * 10), ',', -1), ':', 1) = education.id
WHERE SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(status, ',', 1 + units.i + tens.i * 10), ',', -1), ':', -1)  = 6
AND (units.i + tens.i * 10) <= (LENGTH(status) - LENGTH(REPLACE(status, ',','')));

SQL fiddle for this:-

http://www.sqlfiddle.com/#!2/a2917/5

Kickstart
  • 21,403
  • 2
  • 21
  • 33