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 Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/a2293bbcad5718267933282256b7413c?s=64&d=mm&r=g) |
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
|
|
|
|
|
|
|
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
nitesh.erp@gmail.com wrote on Thu, 23 June 2016 16:28Great 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:44Please 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 #681690 is a reply to message #681688] |
Sun, 16 August 2020 03:08 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/a2293bbcad5718267933282256b7413c?s=64&d=mm&r=g) |
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
|
|
|
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 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.
|
|
|
|
|
|
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Solomon Yakobson
Messages: 3280 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.
|
|
|
|
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Solomon Yakobson
Messages: 3280 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.
|
|
|
|
Goto Forum:
Current Time: Sat Sep 07 23:45:46 CDT 2024
|