How do I parse a comma separated field?

I have a table with the following fields:
tableid (int)
userid (varchar)
lvalues (varchar)

the lvalues column contains comma separted values. e.g. 12, 5, 32
each individual values matches a field in another table.

How do I write a SQL query that parses the comma separated data? For
example:
WHERE user id=30 so that the results look like:

30 | 12
30 | 5
30 |32

TIA

On 14 Jun, 15:03, "John Rappold" <jrapp...@jscoca-k12.org> wrote:
> I have a table with the following fields:
> tableid (int)
> userid (varchar)
> lvalues (varchar)
>
> the lvalues column contains comma separted values. e.g. 12, 5, 32
> each individual values matches a field in another table.
>
> How do I write a SQL query that parses the comma separated data? For
> example:
> WHERE user id=30 so that the results look like:
>
> 30 | 12
> 30 | 5
> 30 |32
>
> TIA

You don't you build a table structure where each value has its own
record in another table.

From: http://dev.mysql.com/tech-resources/...alization.html
"The first normal form (or 1NF) requires that the values in each
column of a table are atomic. By atomic we mean that there are no sets
of values within a column."

This is a lookup table and the data is already there in the comma separated
format. I can't change it.

I'm thinking I need to do some sort of SELECT IN and then create a temporary
table.

Can anyone help me?
"Captain Paralytic" <paul_lautman@yahoo.com> wrote in message
news:1181830481.681955.25380@e9g2000prf.googlegrou ps.com...
> On 14 Jun, 15:03, "John Rappold" <jrapp...@jscoca-k12.org> wrote:
>> I have a table with the following fields:
>> tableid (int)
>> userid (varchar)
>> lvalues (varchar)
>>
>> the lvalues column contains comma separted values. e.g. 12, 5, 32
>> each individual values matches a field in another table.
>>
>> How do I write a SQL query that parses the comma separated data? For
>> example:
>> WHERE user id=30 so that the results look like:
>>
>> 30 | 12
>> 30 | 5
>> 30 |32
>>
>> TIA
>
> You don't you build a table structure where each value has its own
> record in another table.
>
> From:
> http://dev.mysql.com/tech-resources/...alization.html
> "The first normal form (or 1NF) requires that the values in each
> column of a table are atomic. By atomic we mean that there are no sets
> of values within a column."
>

== Quote from John Rappold (jrappold@jscoca-k12.org)'s article
> This is a lookup table and the data is already there in the comma separated
> format. I can't change it.
> I'm thinking I need to do some sort of SELECT IN and then create a temporary
> table.
> Can anyone help me?
> "Captain Paralytic" <paul_lautman@yahoo.com> wrote in message
> news:1181830481.681955.25380@e9g2000prf.googlegrou ps.com...
> > On 14 Jun, 15:03, "John Rappold" <jrapp...@jscoca-k12.org> wrote:
> >> I have a table with the following fields:
> >> tableid (int)
> >> userid (varchar)
> >> lvalues (varchar)
> >>
> >> the lvalues column contains comma separted values. e.g. 12, 5, 32
> >> each individual values matches a field in another table.
> >>
> >> How do I write a SQL query that parses the comma separated data? For
> >> example:
> >> WHERE user id=30 so that the results look like:
> >>
> >> 30 | 12
> >> 30 | 5
> >> 30 |32
> >>
> >> TIA
> >
> > You don't you build a table structure where each value has its own
> > record in another table.
> >
> > From:
> > http://dev.mysql.com/tech-resources/...alization.html
> > "The first normal form (or 1NF) requires that the values in each
> > column of a table are atomic. By atomic we mean that there are no sets
> > of values within a column."
> >

you can use a function called substring_index to separate the values.
--
POST BY: lark with PHP News Reader

On Jun 14, 1:41 pm, lark <ham...@sbcglobal.net> wrote:
> == Quote from John Rappold (jrapp...@jscoca-k12.org)'s article
>
>
>
> > This is a lookup table and the data is already there in the comma separated
> > format. I can't change it.
> > I'm thinking I need to do some sort of SELECT IN and then create a temporary
> > table.
> > Can anyone help me?
> > "Captain Paralytic" <paul_laut...@yahoo.com> wrote in message
> >news:1181830481.681955.25380@e9g2000prf.googlegro ups.com...
> > > On 14 Jun, 15:03, "John Rappold" <jrapp...@jscoca-k12.org> wrote:
> > >> I have a table with the following fields:
> > >> tableid (int)
> > >> userid (varchar)
> > >> lvalues (varchar)
>
> > >> the lvalues column contains comma separted values. e.g. 12, 5, 32
> > >> each individual values matches a field in another table.
>
> > >> How do I write a SQL query that parses the comma separated data? For
> > >> example:
> > >> WHERE user id=30 so that the results look like:
>
> > >> 30 | 12
> > >> 30 | 5
> > >> 30 |32
>
> > >> TIA
>
> > > You don't you build a table structure where each value has its own
> > > record in another table.
>
> > > From:
> > >http://dev.mysql.com/tech-resources/...alization.html
> > > "The first normal form (or 1NF) requires that the values in each
> > > column of a table are atomic. By atomic we mean that there are no sets
> > > of values within a column."
>
> you can use a function called substring_index to separate the values.
> --
> POST BY: lark with PHP News Reader

Please check this link - I think it will do what you want:

http://forge.mysql.com/snippets/view.php?id=4

John Rappold wrote:
> "Captain Paralytic" <paul_lautman@yahoo.com> wrote in message
> news:1181830481.681955.25380@e9g2000prf.googlegrou ps.com...
>> On 14 Jun, 15:03, "John Rappold" <jrapp...@jscoca-k12.org> wrote:
>>> I have a table with the following fields:
>>> tableid (int)
>>> userid (varchar)
>>> lvalues (varchar)
>>>
>>> the lvalues column contains comma separted values. e.g. 12, 5, 32
>>> each individual values matches a field in another table.
>>>
>>> How do I write a SQL query that parses the comma separated data? For
>>> example:
>>> WHERE user id=30 so that the results look like:
>>>
>>> 30 | 12
>>> 30 | 5
>>> 30 |32
>>>
>>> TIA
>> You don't you build a table structure where each value has its own
>> record in another table.
>>
>> From:
>> http://dev.mysql.com/tech-resources/...alization.html
>> "The first normal form (or 1NF) requires that the values in each
>> column of a table are atomic. By atomic we mean that there are no sets
>> of values within a column."
>>
>
> This is a lookup table and the data is already there in the comma separated
> format. I can't change it.
>
> I'm thinking I need to do some sort of SELECT IN and then create a temporary
> table.
>
> Can anyone help me?
>

(Top posting fixed)

Paul is correct. While you might be able to get by with it this time,
you will be much better off in the long run to normalize your database.
Otherwise you will continue to run into similar problems.

In this case, since you have a many-to-many relationship, it's as simple
as building another table. This new table would have two columns - the
id of the original row in your first table, and one of the values from
the multiple-valued column, i.e. (Using names to be clearer - normally I
would be using numeric values for the id's):

Old table
Tom Venice, Rome, Paris, Berlin
**** London, Copenhagen, Paris, Madrid
Harry London, Venice, Rome, Madrid

New table:
Tom Venice
Tom Rome
Tom Paris
Tom Berlin
**** London
**** Copenhagen
**** Paris
**** Madrid
Harry London
Harry Venice
Harry Rome
Harry Madrid

Now it is easy to select who's been to Rome (Tom and Harry), or where
**** has been (London, Copenhagen, Paris and Madrid).

It may be difficult to change this now - but it's going to be even
harder later. And as long as you keep this design you will run into
more problems like this.

P.S. Please don't top post. Thanks.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

anokun7 wrote:
On Jun 14, 1:41 pm, lark <ham...@sbcglobal.net> wrote:
== Quote from John Rappold (jrapp...@jscoca-k12.org)'s article
This is a lookup table and the data is already there in the comma separated
format. I can't change it.
I'm thinking I need to do some sort of SELECT IN and then create a temporary
able.
Can anyone help me?

"Captain Paralytic" <paul_laut...@yahoo.com> wrote in message
news:1181830481.681955.25380@e9g2000prf.googlegrou ps.com...
On 14 Jun, 15:03, "John Rappold" <jrapp...@jscoca-k12.org> wrote:
I have a table with the following fields:
tableid (int)
userid (varchar)
lvalues (varchar)
the lvalues column contains comma separted values. e.g. 12, 5, 32
each individual values matches a field in another table.
How do I write a SQL query that parses the comma separated data? For
example:
WHERE user id=30 so that the results look like:
30 | 12
30 | 5
30 |32
TIA

> You don't you build a table structure where each value has its own
> record in another table.
> From:
> http://dev.mysql.com/tech-resources/...alization.html
> "The first normal form (or 1NF) requires that the values in each
> column of a table are atomic. By atomic we mean that there are no sets
> of values within a column."
> you can use a function called substring_index to separate the values.

> POST BY: lark with PHP News Reader
>
> Please check this link - I think it will do what you want:
>
> http://forge.mysql.com/snippets/view.php?id=4
>

This is how I personally handle that issue:
You can use the LIKE operator to select the comma-delimited values.
Assuming the values are all known, turning them into a new table
shouldn't be too difficult.

CREATE TABLE lv12
SELECT columns FROM table1
WHERE uid='30' AND lvalues LIKE '%12%';

CREATE TABLE lv5
SELECT columns FROM table
WHERE uid='30' AND lvalues LIKE '%5%';

CREATE TABLE lv32
SELECT columns FROM table
WHERE uid='30' AND lvalues LIKE '%32%';

CREATE TABLE newlayout
SELECT lv12.uid AS uid,
lv12.lvalues AS l12,
lv5.lvalues AS l5,
lv32.lvalues AS l32

FROM lv12
LEFT JOIN lv5
ON lv12.uid = lv5.uid
LEFT JOIN lv32
ON lv12.uid = lv32.uid;

There might be a more elegant way to actually seperate the columns, but
the method here will work.

--
Temi Solo Dio

Brian M Napoletano
napzilla@napoletano.net
http://www.napoletano.net