MySQL create view from concat value

Question

Lets say I have a table as follows,

+----+-------------+
| id | value       |
+----+-------------+
| 1  | aa,bb,cc,dd |
| 2  | ee,ff,gg,hh |
+----+-------------+

I want to be able to search this table to see if id = 1 AND value = 'cc'. Im assuming a good way of doing this is to grab the id = 1 row and split its values into separate rows in a new view. Something like,

+-----+
| val |
+-----+
| aa  |
| bb  |
| cc  |
| dd  |
+-----+

I would like to do all of this in MySQL. How can i do this, and is there possibly a better way to do it?

have a look at this: http://stackoverflow.com/questions/3908966/mysql-procedure-to-load-data-from-staging-table-to-other-tables-need-to-split-u/3909888#3909888 — John Woo, Mar 20 '12 at 11:36
Someone posted this, but then deleted it. I think its slightly helpful, so ill repost it for the record. http://blog.fedecarg.com/2009/02/22/mysql-split-string-function/ — cnotethegr8, Mar 20 '12 at 12:13

John Woo · Accepted Answer · 2012-03-20T12:03:36.623

1

This article might help you solve the problem:

Mysql split column string into rows

Instead of this:

DECLARE cur1 CURSOR FOR 
          SELECT table1.id, table1.value
          FROM table1
          WHERE table1.value != '';

try this to fit your needs,

DECLARE cur1 CURSOR FOR 
          SELECT id, value
          FROM table1
          WHERE id = 1;

edited Mar 20 '12 at 12:03

answered Mar 20 '12 at 11:41

John Woo

258,903
69
498
492

score 0 · Answer 2 · answered Mar 20 '12 at 11:27

0

Its not good way but you can do string search like this

...WHERE id = 1 and 
(value like ',cc,' OR 
 substring(value, -1*LENGTH('cc')-1) = ',cc' OR 
 substring(value, 0, LENGTH('cc')+1) = 'cc,')

answered Mar 20 '12 at 11:27

safarov

7,793
2
36
52

MySQL create view from concat value

2 Answers2