Home » SQL & PL/SQL » SQL & PL/SQL » regexp_substr fix string unable to handle. (Oracle, 11g, Windows 7)
regexp_substr fix string unable to handle. [message #681682] Sun, 16 August 2020 02:28 Go to next message
nitesh.erp@gmail.com
Messages: 24
Registered: June 2016
Junior Member
Dear Sir,

I am unable to handle our requirement, please help.

I have below query.

select regexp_substr('COMPLETE//WELLNESS//PRIMARY//ABS/POLY//MATERIALS//SHEET//','[^//]+',1,4) BRAND from dual;

It's return 'ABS' but I want 'ABS/POLY'

Please help, Thanks

Report message to a moderator

regexp_substr fix string unable to handle. [message #681683 is a reply to message #681682] Sun, 16 August 2020 02:29 Go to previous messageGo to next message
nitesh.erp@gmail.com
Messages: 24
Registered: June 2016
Junior Member
Dear Sir,

I am unable to handle our requirement, please help.

I have below query.

select regexp_substr('COMPLETE//WELLNESS//PRIMARY//ABS/POLY//MATERIALS//SHEET//','[^//]+',1,4) BRAND from dual;

It's return 'ABS' but I want 'ABS/POLY'

Please help, Thanks

Report message to a moderator

regexp_substr fix string unable to handle. [message #681684 is a reply to message #681682] Sun, 16 August 2020 02:30 Go to previous messageGo to next message
nitesh.erp@gmail.com
Messages: 24
Registered: June 2016
Junior Member
Dear Sir,

I am unable to handle our requirement, please help.

I have below query.

select regexp_substr('COMPLETE//WELLNESS//PRIMARY//ABS/POLY//MATERIALS//SHEET//','[^//]+',1,4) BRAND from dual;

It's return 'ABS' but I want 'ABS/POLY'

Please help, Thanks

Report message to a moderator

Re: regexp_substr fix string unable to handle. [message #681685 is a reply to message #681682] Sun, 16 August 2020 02:42 Go to previous messageGo to next message
John Watson
Messages: 8804
Registered: January 2010
Location: Global Village
Senior Member
Please read the OraFAQ Forum Guide and How to use code tags and make your code easier to read

Report message to a moderator

Re: regexp_substr fix string unable to handle. [message #681686 is a reply to message #681685] Sun, 16 August 2020 02:45 Go to previous messageGo to next message
nitesh.erp@gmail.com
Messages: 24
Registered: June 2016
Junior Member
I have below query.

select regexp_substr('COMPLETE//WELLNESS//PRIMARY//ABS/POLY//MATERIALS//SHEET//','[^//]+',1,4) BRAND from dual;
It's return 'ABS' but I want 'ABS/POLY'

Report message to a moderator

Re: regexp_substr fix string unable to handle. [message #681687 is a reply to message #681682] Sun, 16 August 2020 02:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68418
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

nitesh.erp@gmail.com wrote on Thu, 23 June 2016 16:28
Great Sir, will reply tomorrow after reach office. Thanks a lot


Still waiting...

Michel Cadot wrote on Sat, 23 November 2019 19:05

...
In addition, Please read How to use [code] tags and make your code easier to read.

BlackSwan wrote on Sun, 24 November 2019 03:44
Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

Michel Cadot wrote on Sun, 24 November 2019 14:07

1/ Format your posts.
...
...

[Updated on: Sun, 16 August 2020 02:48]

Report message to a moderator

Re: regexp_substr fix string unable to handle. [message #681688 is a reply to message #681682] Sun, 16 August 2020 02:55 Go to previous messageGo to next message
akssre
Messages: 26
Registered: March 2018
Junior Member
I am sure there are many ways to achieve what your are looking for, I have tried as follows:- 

select substr('COMPLETE//WELLNESS//PRIMARY//ABS/POLY//MATERIALS//SHEET//',
              (regexp_instr('COMPLETE//WELLNESS//PRIMARY//ABS/POLY//MATERIALS//SHEET//',
                            '[^//]+',
                            1,
                            4)),
              8)
  from dual;

Report message to a moderator

Re: regexp_substr fix string unable to handle. [message #681689 is a reply to message #681686] Sun, 16 August 2020 03:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68418
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It depends on your actual and complete specifications.
Here are 2 ways for this specific data:
SQL> select regexp_replace('COMPLETE//WELLNESS//PRIMARY//ABS/POLY//MATERIALS//SHEET//',
  2                        '^[^/]+//[^/]+//[^/]+//([^/]+/[^/]+)//.*$',
  3                        '\1') res
  4  from dual
  5  /
RES
--------
ABS/POLY

1 row selected.

SQL> select regexp_substr('COMPLETE//WELLNESS//PRIMARY//ABS/POLY//MATERIALS//SHEET//',
  2                       '[^/]+/[^/]+') res
  3  from dual
  4  /
RES
--------
ABS/POLY

1 row selected.

Report message to a moderator

Re: regexp_substr fix string unable to handle. [message #681690 is a reply to message #681688] Sun, 16 August 2020 03:08 Go to previous messageGo to next message
nitesh.erp@gmail.com
Messages: 24
Registered: June 2016
Junior Member
Actual I have different column need to bifurcate as per below query.
SELECT REGEXP_SUBSTR (
          'COMPLETE//WELLNESS//PRIMARY//ABS/POLY//MATERIALS//SHEET//',
          '[^//]+',
          1,
          1) vertical,
       REGEXP_SUBSTR (
          'COMPLETE//WELLNESS//PRIMARY//ABS/POLY//MATERIALS//SHEET//',
          '[^//]+',
          1,
          2) division,
       REGEXP_SUBSTR (
          'COMPLETE//WELLNESS//PRIMARY//ABS/POLY//MATERIALS//SHEET//',
          '[^//]+',
          1,
          3) brand_group,
       REGEXP_SUBSTR (
          'COMPLETE//WELLNESS//PRIMARY//ABS/POLY//MATERIALS//SHEET//',
          '[^//]+',
          1,
          4) brand,
       REGEXP_SUBSTR (
          'COMPLETE//WELLNESS//PRIMARY//ABS/POLY//MATERIALS//SHEET//',
          '[^//]+',
          1,
          5) range,
       REGEXP_SUBSTR (
          'COMPLETE//WELLNESS//PRIMARY//ABS/POLY//MATERIALS//SHEET//',
          '[^//]+',
          1,
          6) product   
  FROM DUAL;


Data coming like below					
VERTICAL	DIVISION	BRAND_GROUP	BRAND	RANGE	PRODUCT
COMPLETE	WELLNESS	PRIMARY	        ABS	POLY	MATERIALS
					
But my requirement like below					
VERTICAL	DIVISION	BRAND_GROUP	BRAND	        RANGE	        PRODUCT
COMPLETE	WELLNESS	PRIMARY	        ABS/POLY	MATERIALS	SHEET

Report message to a moderator

Re: regexp_substr fix string unable to handle. [message #681691 is a reply to message #681688] Sun, 16 August 2020 03:10 Go to previous messageGo to next message
Michel Cadot
Messages: 68418
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

@akssre

'[^//]+' is the same thing than '[^/]+', the square brackets indicate a set of characters not a string:
SQL> select regexp_instr('COMPLETE//WELLNESS//PRIMARY//ABS/POLY//MATERIALS//SHEET//','[^//]+',1,6) from dual;
REGEXP_INSTR('COMPLETE//WELLNESS//PRIMARY//ABS/POLY//MATERIALS//SHEET//','[^//]+',1,6)
--------------------------------------------------------------------------------------
                                                                                    40

1 row selected.

SQL> select regexp_instr('COMPLETE//WELLNESS//PRIMARY//ABS/POLY//MATERIALS//SHEET//','[^/]+',1,6) from dual;
REGEXP_INSTR('COMPLETE//WELLNESS//PRIMARY//ABS/POLY//MATERIALS//SHEET//','[^/]+',1,6)
-------------------------------------------------------------------------------------
                                                                                   40

1 row selected.

Report message to a moderator

Re: regexp_substr fix string unable to handle. [message #681692 is a reply to message #681690] Sun, 16 August 2020 03:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68418
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Bad specification => inaccurate solution.

SQL> with
  2    data as (
  3      select 'COMPLETE//WELLNESS//PRIMARY//ABS/POLY//MATERIALS//SHEET//' val
  4      from dual
  5    )
  6  select regexp_substr(replace(val,'//','#'), '[^#]+', 1, level) res
  7  from data
  8  connect by level <= 6
  9  /
RES
---------------------------------------------------
COMPLETE
WELLNESS
PRIMARY
ABS/POLY
MATERIALS
SHEET

Report message to a moderator

Re: regexp_substr fix string unable to handle. [message #681693 is a reply to message #681692] Sun, 16 August 2020 03:47 Go to previous messageGo to next message
nitesh.erp@gmail.com
Messages: 24
Registered: June 2016
Junior Member
Sir, I have to breakup string

'COMPLETE//WELLNESS//PRIMARY//ABS/POLY//MATERIALS//SHEET//'

like below

VERTICAL	DIVISION	BRAND_GROUP	BRAND	        RANGE	        PRODUCT
COMPLETE	WELLNESS	PRIMARY	        ABS/POLY	MATERIALS	SHEET

replace with '#' make us another exception, string might be have '#' value in mid, then it will break the string, we have only given '//' to bifurcate.
please help me.

Report message to a moderator

Re: regexp_substr fix string unable to handle. [message #681697 is a reply to message #681693] Sun, 16 August 2020 08:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68418
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You can use any character, # is just an example, I think you have not "chr(0)" in your string, have you?

[Updated on: Sun, 16 August 2020 10:43]

Report message to a moderator

Re: regexp_substr fix string unable to handle. [message #681698 is a reply to message #681697] Sun, 16 August 2020 11:09 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3212
Registered: January 2010
Location: Connecticut, USA
Senior Member
Or without relying on string not containing certain character:

select  regexp_substr('COMPLETE//WELLNESS//PRIMARY//ABS/POLY//MATERIALS//SHEET//','(.+?)(//)',1,level,null,1) BRAND
  from  dual
        connect by level <= 6
/

BRAND
----------
COMPLETE
WELLNESS
PRIMARY
ABS/POLY
MATERIALS
SHEET

6 rows selected.

SQL>
But I have a feeling in reality OP has not a single string but rather a table. If so:

with sample as (
                select 'COMPLETE//WELLNESS//PRIMARY//ABS/POLY//MATERIALS//SHEET//' brand_list from dual union all
                select 'FULL/COMPLETE//INCOMPLTE/MISSING//SECONDARY//' from dual
               )
select  regexp_substr(brand_list,'(.+?)(//)',1,lvl,null,1) brand
  from  sample,
        lateral(
                select  level lvl
                  from  dual
                  connect by level <= regexp_count(brand_list,'//')
               )
/

BRAND
-----------------
COMPLETE
WELLNESS
PRIMARY
ABS/POLY
MATERIALS
SHEET
FULL/COMPLETE
INCOMPLTE/MISSING
SECONDARY

9 rows selected.

SQL>
SY.

Report message to a moderator

Re: regexp_substr fix string unable to handle. [message #681699 is a reply to message #681698] Sun, 16 August 2020 11:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68418
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
But I have a feeling in reality OP has not a single string but rather a table.

Well, I just use the row generator because I was too lazy to write 6 times the same expression. He actually wants all values of a row in different columns of a single row.


I once again forgot the question mark. Smile

Report message to a moderator

Re: regexp_substr fix string unable to handle. [message #681700 is a reply to message #681699] Sun, 16 August 2020 18:42 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3212
Registered: January 2010
Location: Connecticut, USA
Senior Member
That assumes number of elements in the string doesn't exceed six. And I'd use substr/instr for better performance:

with sample as (
                select 'COMPLETE//WELLNESS//PRIMARY//ABS/POLY//MATERIALS//SHEET//' brand_list from dual union all
                select 'FULL/COMPLETE//INCOMPLTE/MISSING//SECONDARY//' from dual
               )
select  substr(brand_list,1,instr(brand_list,'//') - 1) brand1,
        substr(brand_list,instr(brand_list,'//') + 2,instr(brand_list,'//',1,2) - instr(brand_list,'//') - 2) brand2,
        substr(brand_list,instr(brand_list,'//',1,2) + 2,instr(brand_list,'//',1,3) - instr(brand_list,'//',1,2) - 2) brand3,
        substr(brand_list,instr(brand_list,'//',1,3) + 2,instr(brand_list,'//',1,4) - instr(brand_list,'//',1,3) - 2) brand4,
        substr(brand_list,instr(brand_list,'//',1,4) + 2,instr(brand_list,'//',1,5) - instr(brand_list,'//',1,4) - 2) brand5,
        substr(brand_list,instr(brand_list,'//',1,5) + 2,instr(brand_list,'//',1,6) - instr(brand_list,'//',1,5) - 2) brand6
  from  sample
/

BRAND1            BRAND2            BRAND3            BRAND4            BRAND5            BRAND6
----------------- ----------------- ----------------- ----------------- ----------------- -----------------
COMPLETE          WELLNESS          PRIMARY           ABS/POLY          MATERIALS         SHEET
FULL/COMPLETE     INCOMPLTE/MISSING SECONDARY

SQL>
SY.

Report message to a moderator

Re: regexp_substr fix string unable to handle. [message #681701 is a reply to message #681700] Sun, 16 August 2020 23:28 Go to previous message
nitesh.erp@gmail.com
Messages: 24
Registered: June 2016
Junior Member
Thanks to everyone, all seniors are really very genius. I have used instr to bifurcate segment, it works foolproof.
Thanks Michel Cadot sir.

Report message to a moderator

Previous Topic: dbms_output.put_line synchronize
Next Topic: SQL CASE or IF Else Condition
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Mar 28 03:21:32 CDT 2023
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.