Help with the SQL [message #598810] |
Thu, 17 October 2013 16:20 |
|
ushelke
Messages: 2 Registered: October 2013
|
Junior Member |
|
|
Colum field value: ALPHA_B3_TRAN,ALPHA_A3_PROD,ALPHA_C3_FX,ALPHA_B3_ACCT,ALPHA_D3_PRICE
Desired Output: B3,A3,C3,D3
Colum field value: ALPHA_B3_TRAN,ALPHA_B3_PROD,ALPHA_B3_FX,ALPHA_B3_ACCT,ALPHA_B3_PRICE
Desired Output: B3
How can i get the desired output using the SQL statement.
|
|
|
Re: Help with the SQL [message #598816 is a reply to message #598810] |
Thu, 17 October 2013 16:45 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
1. But what are you doing?
2. There are 2 different outputs for the same input column values.
3. What are B3,A3,C3?
4. Use SQL*Plus to post the complete session output.
Please specify the RULES to generate the output.
[Updated on: Thu, 17 October 2013 16:48] Report message to a moderator
|
|
|
|
Re: Help with the SQL [message #598820 is a reply to message #598817] |
Thu, 17 October 2013 16:49 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
dariyoosh wrote on Fri, 18 October 2013 03:16If extracting A3, B3, C3, D3 from column values, that is, every token between a pair of underscores, is what you're asking for then you may have a look at regexp_substr
Nothing could be concluded without knowing the RULES to generate the output.
|
|
|
Re: Help with the SQL [message #598833 is a reply to message #598816] |
Thu, 17 October 2013 19:02 |
|
ushelke
Messages: 2 Registered: October 2013
|
Junior Member |
|
|
Hi Lalit/Dariyoosh,
Thanks for getting back. Here what I am trying to achieve.
I have table with data something like in the column.
ALPHA_B3_TRAN,ALPHA_A3_PROD,ALPHA_C3_FX,ALPHA_B3_ACCT,ALPHA_D3_PRICE
ALPHA_B3_TRAN,ALPHA_B3_PROD,ALPHA_B3_FX,ALPHA_B3_ACCT,ALPHA_B3_PRICE
ALPHA_A2_PROD,ALPHA_X3_FX,ALPHA_X3_ACCT
ALPHA_D3_ACCT,ALPHA_S3_PRICE
ALPHA_P3_TRAN,ALPHA_P5_PRICE
I would like to query this column to get the output as
B3,A3,C3,D3
B3
A2,X3
D3,S3
P3,P5
If the value between two underscore is appearing multiple times, the it should be printed only once. In the above example for row 2, B3 should appear in output only once.
Lalit- I thought of using REGEXP_SUBSTR but it started showing the value between first and last underscore. Since not much exposure to REGEXP_SUBSTR finding it difficult.
|
|
|
|