OraFAQ Forum: SQL & PL/SQL » Substring Query in Oracle SQL

Home » SQL & PL/SQL » SQL & PL/SQL » Substring Query in Oracle SQL (Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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

Report message to a moderator

Re: Substring Query in Oracle SQL [message #683404 is a reply to message #683403]

Fri, 08 January 2021 05:08

Michel Cadot
Messages: 68634
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.

Report message to a moderator

Re: Substring Query in Oracle SQL [message #683405 is a reply to message #683403]

Fri, 08 January 2021 05:25

abhayman
Messages: 37
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;

Report message to a moderator

Re: Substring Query in Oracle SQL [message #683406 is a reply to message #683405]

Fri, 08 January 2021 05:29

Michel Cadot
Messages: 68634
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.

Report message to a moderator

Re: Substring Query in Oracle SQL [message #683407 is a reply to message #683405]

Fri, 08 January 2021 05:58

John Watson
Messages: 8927
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.

Report message to a moderator

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: 8927
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.

Report message to a moderator

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.

Report message to a moderator

Re: Substring Query in Oracle SQL [message #683411 is a reply to message #683407]

Fri, 08 January 2021 06:35

Solomon Yakobson
Messages: 3269
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.

Report message to a moderator

Previous Topic:	DBMS_HS_PASSTHROUGH - Database Link as Input Parameter
Next Topic:	After "Drop Materialized View" it's data continuing be shown

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 16 02:30:40 CDT 2024