[plug] Re: [OT] SQL column splitting

[James Devenish]

In message <41994E2E.6010802 at book-keepingnetwork.com.au>
on Tue, Nov 16, 2004 at 08:47:42AM +0800, Alex Polglaze wrote:
> Is the data in a .csv file to start with? How are the sub values
> 'x,y,z' separated from '1' and 'A'?

The values are in the DB to begin with. They arrive via SQL and leave
via SQL :) Thanks anyway. (The history of the problem is that 'x,y, z'
had previously been 'assumed' to be opaque, but it isn't really.)

I suppose that if I knew how many commas were in the 'x,y, z', I could
join with a table of numbers (WHERE Number <= CHAR_COUNT(',', 'x,y, z'))
so that ('1', 'A', 'x,y, z') would become ('1', 'A', 'x,y, z', '1'),
('1', 'A', 'x,y, z', '2'), ('1', 'A', 'x,y, z', '3'), which is easily
reinterpreted as ('1', 'A', 'x', '1'), ('1', 'A', 'y', '2'), ('1', 'A',
' z', '3'). Eek. I don't have wide enough SQL exposure to know about the
sanity of this remedial approach, but I'm shying away from it at the
moment.