[plug] Re: [OT] SQL column splitting

[Onno Benschop]

On Mon, 2004-11-15 at 18:23, James Devenish wrote:
> In message <20041115101826.GA11461 at mail.guild.uwa.edu.au>
> on Mon, Nov 15, 2004 at 06:18:26PM +0800, James Devenish wrote:
> > I want like to split
> > ('1', 'A', 'x,y, z'), ('2', 'B', 'i')
> > into
> > ('1', 'A', 'x'), ('1', 'A', 'y'), ('1', 'A', ' z'), ('2', 'B', 'i')
> 
> Just to be clear about the sort of solutions I am after... The table
> contains entries like 'x,y, z' because of the nature of its application.
> Since ',' is a delimiter in this case, my problem is one of "converting"
> the data to a more relational model. However, because circumstances
> prevent this from being solved with a one-off migration, I would like
> to be able to do it dynamically in SQL.

Well,

The comma allows for an SQL IN construct, SELECT blah WHERE blah IN blah

Depending on how "dirty" the data is, you could define the field that
contains the "x,y, z" as an enumerated field and select based on
enumField+0, which returns the internal storage format.

Does that help?

Onno Benschop 

Connected via Telstra BigPond because I cannot see Optus B3 where I am :-(
-- 
()/)/)()        ..ASCII for Onno.. 
|>>?            ..EBCDIC for Onno.. 
--- -. -. ---   ..Morse for Onno.. 

Proudly supported by Skipper Trucks, Highway1, Concept AV, Sony Central, Dalcon
ITmaze - ABN: 56 178 057 063 - ph: 04 1219 8888 - onno at itmaze dot com dot au