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 Go to next message
abhayman
Messages: 35
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 #683404 is a reply to message #683403] Fri, 08 January 2021 05:08 Go to previous messageGo to next message
Michel Cadot
Messages: 67888
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Re: Substring Query in Oracle SQL [message #683405 is a reply to message #683403] Fri, 08 January 2021 05:25 Go to previous messageGo to next message
abhayman
Messages: 35
Registered: August 2011
Location: CA
Member
CREATE Table Statement

CREATE TABLE TEST(
COL1 VARCHAR2(300) NOT NULL,
COL2 VARCHAR2(50)
);

My trial which I know is wrong
select 'PQR:NOM:XYN:SDF:RST:EDF' as col1 , 'now' as col2 , REGEXP_SUBSTR('PQR:NOM:XYN:SDF:RST:EDF',col2) from dual;

Re: Substring Query in Oracle SQL [message #683406 is a reply to message #683405] Fri, 08 January 2021 05:29 Go to previous messageGo to next message
Michel Cadot
Messages: 67888
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Fri, 08 January 2021 12:08

Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
Re: Substring Query in Oracle SQL [message #683407 is a reply to message #683405] Fri, 08 January 2021 05:58 Go to previous messageGo to next message
John Watson
Messages: 8560
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 Go to previous messageGo to next message
abhayman
Messages: 35
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 Go to previous messageGo to next message
John Watson
Messages: 8560
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 Go to previous messageGo to next message
abhayman
Messages: 35
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 Go to previous message
Solomon Yakobson
Messages: 3063
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.
Previous Topic: DBMS_HS_PASSTHROUGH - Database Link as Input Parameter
Next Topic: After "Drop Materialized View" it's data continuing be shown
Goto Forum:
  


Current Time: Wed Jun 23 04:17:42 CDT 2021