From: Chris Gamache <cgg007@yahoo.com>
To: pgsql-sql@postgresql.org
Subject: Splitting text into rows with SQL
Date: Fri, 7 Mar 2003 14:14:01 -0800 (PST)
Message-ID: <20030307221401.19809.qmail@web13804.mail.yahoo.com> (raw)
Using Postgresql 7.2.3 ...
In order to search using indexes I need to split a composite field into its
components and return it as rows... If this would only work:
<theoretical code>
create table table_with_composite_fields (
data1 serial,
data2 varchar(100),
composite_field text
);
insert into table_with_composite_fields (data2, composite_field) values
('something1','00000000-0000-0000-0000000000000000,11111111-1111-1111-1111111111111111,22222222-2222-2222-2222222222222222');
create table other_table (
data3 serial,
data4 varchar(100),
uuid uniqueidentifier
);
create index 'other_table_uuid_idx' on other_table(uuid);
insert into other_table (data4, uuid) values
('something2','00000000-0000-0000-0000000000000000');
insert into other_table (data4, uuid) values
('something3','11111111-1111-1111-1111111111111111');
insert into other_table (data4, uuid) values
('something4','22222222-2222-2222-2222222222222222');
select * from other_table ot where ot.uuid in (select split(composite_field)
from table_with_composite_field where data1=1) order by data3;
data3 | data4 | uuid
-------+------------+-------------------------------------
1 | something2 | 00000000-0000-0000-0000000000000000
2 | something3 | 11111111-1111-1111-1111111111111111
3 | something4 | 22222222-2222-2222-2222222222222222
</theoretical code>
any ideas for creating my fictional "split" function? I don't mind if the
solution is head-slapping-ly simple. I'm too close to the problem and can't
seem to figure it out!
CG
__________________________________________________
Do you Yahoo!?
Yahoo! Tax Center - forms, calculators, tips, more
http://taxes.yahoo.com/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: cgg007@yahoo.com
Subject: Re: Splitting text into rows with SQL
In-Reply-To: <20030307221401.19809.qmail@web13804.mail.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox