Re: sql question
- From: Mathias Magnusson <mathias.magnusson@xxxxxxxxx>
- To: eugene.pipko@xxxxxxxxxxxx, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 17 Apr 2009 08:20:25 +0200
I managed to miss the list again...
I think a key thing is to know the version as without it we have to write a
version of this that handles at least a few different versions. The key
issue is probably not how we pull it out from the data, but rather if
selecting twice from the table will be a problem.
Mathias
Depending on version you may be able to use some of the fancy XML features,
> but this should work even in fairly old versions.
>
> with a as (select '338K10_340W81' hts_code, '6109.10.0027 & 6205.20.2061'
> hts_desc from dual
> union all
> select '338K5_338K9_1' hts_code, '6105.10.0030 & 6110.20.2069'
> hts_desc from dual)
> select hts_code, trim(substr(hts_desc,1,instr(hts_desc, '&') - 1)) from a
> union all
> select hts_code, trim(substr(hts_desc,instr(hts_desc, '&') + 1)) from a
>
> As you work on the same row twice, you will probably want to use a with
> construct to read and filter the rows you need. That depends on the volume
> of course.
>
> Mathias
>
>
> On Fri, Apr 17, 2009 at 1:47 AM, Eugene Pipko
> <eugene.pipko@xxxxxxxxxxxx>wrote:
>
>> 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.
>>
>>
>>
>
>
Other related posts:
