Re: sql question

From: Jack van Zanen <jack_at_vanzanen.com>
Date: Fri, 17 Apr 2009 10:56:34 +1000
Message-ID: <77a615a70904161756y15280d7o8fe32d00c4bd2ac_at_mail.gmail.com>

If the format of hts_desc is always the same (xxxxxxx & yyyyyyy) than the following will work. It is highly dependent on the format of column hts_desc though. An additional space will already mess it up and ommiting or adding one entry with an additional "&" is definately a NO NO.

but

You can probably get around the inconsistencies in the format by creating some PL/SQL to parse it and stick it in a table function so you can still do a "normal" select

select hts_code, substr(hts_desc,1,instr(hts_desc,'&','1')-2) from hts union all
select hts_code, substr(hts_desc,instr(hts_desc,'&','1')+2) from hts

Jack

2009/4/17 Eugene Pipko <eugene.pipko_at_unionbay.com>

> Hi all,
>
> I have a table with the following columns:
>
>
>
> HTS_CODE HTS_DESC
>
> -----------------------
> -----------------------------------------
>
> 338K10_340W81 6109.10.0027 & 6205.20.2061
>
> 338K5_338K9_1 6105.10.0030 & 6110.20.2069
>
>
>
> I need to be able to select in the following format:
>
>
>
> HTS_CODE HTS_DESC
>
> -----------------------
> -----------------------------------------
>
> 338K10_340W81 6109.10.0027
>
> 338K10_340W81 6205.20.2061
>
> 338K5_338K9_1 6105.10.0030
>
> 338K5_338K9_1 6110.20.2069
>
>
>
> Is it possible to split hts_desc so for every value before and after “&” I
> will get a row using SQL?
>
> Thanks,
>
>
>
>
>
> Eugene Pipko
>
> Seattle Pacific Industries
>
> office: 253.872.5243
>
> cell: 206.304.7726
>
> P Please consider the environment before printing this e-mail.
>
>
>

-- 
Jack van Zanen

-------------------------
This e-mail and any attachments may contain confidential material for the
sole use of the intended recipient. If you are not the intended recipient,
please be aware that any disclosure, copying, distribution or use of this
e-mail or any attachment is prohibited. If you have received this e-mail in
error, please contact the sender and delete all copies.
Thank you for your cooperation

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Apr 16 2009 - 19:56:34 CDT