Home » SQL & PL/SQL » SQL & PL/SQL » Substring Query in Oracle SQL (Oracle 11g)
Substring Query in Oracle SQL [message #683403] |
Fri, 08 January 2021 04:58 |
|
abhayman
Messages: 37 Registered: August 2011 Location: CA
|
Member |
|
|
Hi,
I have a table with 2 column in below format . I want to get all strings from Col1 which starts from end of word of col2 as separate columns. For example: If Col1 has ABC:EFG:MNO:XYZ and Col2 has MNO . Then output would L1 as XYZ ( data between string of Col2 and end of Col1)
COL1 ----------------------- COL2
ABC:EFG:MNO:XYZ ------------ MNO
PQR:NOM:XYN:SDF:RST:EDF----- NOM
I am trying to get data in below format
L1 ----- L2 ----- L3 ---- L4 ---- L5
XYZ
XYN-----SDF-------RST-----EDF
I am unable to do it using substring.
Any guidance ?
** Sorry if the table structure is not in format for better UX
|
|
|
|
|
|
Re: Substring Query in Oracle SQL [message #683407 is a reply to message #683405] |
Fri, 08 January 2021 05:58 |
John Watson
Messages: 8938 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Something like this should do,orclz>
orclz> CREATE TABLE TEST(
2 COL1 VARCHAR2(300) NOT NULL,
3 COL2 VARCHAR2(50)
4 );
Table created.
orclz> insert into test values('ABC:EFG:MNO:XYZ','MNO');
1 row created.
orclz> insert into test values('PQR:NOM:XYN:SDF:RST:EDF','NOM');
1 row created.
orclz>
orclz> select substr(col1,instr(col1,col2)) from test;
SUBSTR(COL1,INSTR(COL1,COL2))
-------------------------------------------------------------------------------------------------------------------------------
MNO:XYZ
NOM:XYN:SDF:RST:EDF
orclz> please note:
a. I have completed your test case by adding the INSERT statements. Please do not be so lazy in future, there is no reason why you should expect other people to that sort of thing for you.
b. I have used [code] tags to format the post correctly. Your refusal to use them is both lazy and rude.
c. There is no need for regexp, instr and substr are often simpler and faster.
|
|
|
Re: Substring Query in Oracle SQL [message #683408 is a reply to message #683407] |
Fri, 08 January 2021 06:17 |
|
abhayman
Messages: 37 Registered: August 2011 Location: CA
|
Member |
|
|
Thank You . The issue is that I dont want Col1 to appear. It should only delta
XYZ for Row 1
XYN:SDF:RST:EDF for Row2
Also, apologies . I didnt want to be rude or lazy . I am not a frequent developer or user of community groups so I find it bit difficult to update in given format due to my lack of knowledge.
[Updated on: Fri, 08 January 2021 06:22] Report message to a moderator
|
|
|
Re: Substring Query in Oracle SQL [message #683409 is a reply to message #683408] |
Fri, 08 January 2021 06:20 |
John Watson
Messages: 8938 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Shall I send you a quotation for consulting services?
Or if you prefer to do a little work yourself, please be sure to post the results (properly formated) with any questions.
|
|
|
Re: Substring Query in Oracle SQL [message #683410 is a reply to message #683409] |
Fri, 08 January 2021 06:31 |
|
abhayman
Messages: 37 Registered: August 2011 Location: CA
|
Member |
|
|
SELECT
REGEXP_SUBSTR(substr(col1,instr(col1,col2)) ,'[^:]+', 1, 2) AS L2,
REGEXP_SUBSTR(substr(col1,instr(col1,col2)) ,'[^:]+', 1, 3) AS L3,
REGEXP_SUBSTR(substr(col1,instr(col1,col2)) ,'[^:]+', 1, 4) AS L4,
REGEXP_SUBSTR(substr(col1,instr(col1,col2)) ,'[^:]+', 1, 5) AS L5
FROM TEST;
Thank you for your guidance I was able to do it.
|
|
|
Re: Substring Query in Oracle SQL [message #683411 is a reply to message #683407] |
Fri, 08 January 2021 06:35 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
This can produce wrong results. E.g.
insert into test values('ABC:DEFMNOEFG:MNO:XYZ','MNO');
select substr(col1,instr(col1,col2)) from test;
SUBSTR(COL1,INSTR(COL1,COL2))
-----------------------------
MNOEFG:MNO:XYZ
SQL>
Something like:
select substr(col1,instr(':' || col1 || ':',':' || col2 || ':')) from test;
SUBSTR(COL1,INSTR(':'||COL1||':',':'||COL2||':'))
-------------------------------------------------
MNO:XYZ
SQL>
SY.
|
|
|
Goto Forum:
Current Time: Sat Jun 29 07:38:07 CDT 2024
|