| How can i use Substr to get required result [message #574150] |
Mon, 07 January 2013 07:54  |
mamalik
Messages: 247
Registered: November 2008
Location: Pakistan
|
Senior Member |
 
|
|
Dear All i have following table
CREATE TABLE THREAD_SHADES
(
ITM_COD NUMBER NOT NULL,
ITM_DES VARCHAR2(250 BYTE) NOT NULL,
)
Insert into THREAD_SHADES
(ITM_COD, ITM_DES)
Values
(80064186, 'THREAD TEX-105 SHADE# 7921 (1500 MTRS)');
Insert into THREAD_SHADES
(ITM_COD, ITM_DES)
Values
(80064187, 'THREAD TEX-40 SHADE#7921 (3000 MTRS)');
Insert into THREAD_SHADES
(ITM_COD, ITM_DES)
Values
(80114482, 'THREAD TEX 40 SHADE C-8762 1500MTR LOCAL');
Insert into THREAD_SHADES
(ITM_COD, ITM_DES)
Values
(80130541, 'THREAD TEX-60 SHADE C8676 J&P COAST ASTRA 1000 MTRS');
COMMIT;
Select * from Thread_Shades;
Result is
ITM_COD ITM_DES
80064186 THREAD TEX-105 SHADE# 7921 (1500 MTRS)
80064187 THREAD TEX-40 SHADE#7921 (3000 MTRS)
80114482 THREAD TEX 40 SHADE C-8762 1500MTR LOCAL
80130541 THREAD TEX-60 SHADE C8676 J&P COAST ASTRA 1000 MTRS
I want a query which should return only Sahde # from above data result must be as
7921
C-8762
C-8762
C8676
Thanks a lot.
Asif
[EDITED by LF: @mamalik opened a new topic where he specified database version; that one has been deleted; version information copied over here]
[Updated on: Tue, 08 January 2013 00:14] by Moderator Report message to a moderator |
|
|
|
| Re: Oracle 10g [message #574151 is a reply to message #574150] |
Mon, 07 January 2013 08:00  |
joy_division
Messages: 4267
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
I do not understand the second value.
Regular expressions will be an easier way to get only the number part of a string.
|
|
|
|
| Re: Oracle 10g [message #574153 is a reply to message #574151] |
Mon, 07 January 2013 08:06 |
cookiemonster
Messages: 9156
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
|
You've got 4 different formats for the number in your example. Are they the only possible formats or are there more?
|
|
|
|
|
|
| Re: How can i get this using regular function reg_substr [message #574170 is a reply to message #574165] |
Mon, 07 January 2013 10:16 |
_jum
Messages: 451
Registered: February 2008
|
Senior Member |
|
|
Would use regexp_replace instead:
SELECT itm_cod, itm_des,
regexp_replace(itm_des,'.+SHADE[# ]*([C-]*[[:digit:]]+).*','\1') regex
FROM thread_shades;
ITM_COD ITM_DES REGEX
------------------------------------------------------------------------------
80064186 THREAD TEX-105 SHADE# 7921 (1500 MTRS) 7921
80064187 THREAD TEX-40 SHADE#7921 (3000 MTRS) 7921
80114482 THREAD TEX 40 SHADE C-8762 1500MTR LOCAL C-8762
80130541 THREAD TEX-60 SHADE C8676 J&P COAST ASTRA 1000 MTRS C8676
The first part looks for a pattern ending with 'SHADE' and then optional '#' or ' '
the second part in round brackets references the wanted result as \1
the third part matches the rest.
[Updated on: Mon, 07 January 2013 10:38] Report message to a moderator |
|
|
|
|
|
| Re: Oracle 10g [message #574187 is a reply to message #574150] |
Mon, 07 January 2013 13:23 |
Solomon Yakobson
Messages: 1399
Registered: January 2010
|
Senior Member |
|
|
And without regular expressions:
with t as (
select ltrim(
substr(
itm_des,
instr(
itm_des,
'SHADE'
) + 6
)
) itm_des
from thread_shades
)
select substr(
itm_des,
1,
instr(
itm_des,
' ') - 1
) itm_des
from t
/
ITM_DES
--------
7921
7921
C-8762
C8676
SQL>
SY.
|
|
|
|
|
|
| Re: How can i get this using regular function reg_substr [message #574347 is a reply to message #574337] |
Wed, 09 January 2013 05:51 |
Solomon Yakobson
Messages: 1399
Registered: January 2010
|
Senior Member |
|
|
Yes, it is possible with single regexp_substr, but only starting 11g where regexp_substr has additional parameter - subexpression:
select itm_cod,
regexp_substr(
itm_des,
'SHADE((# *)|( +))([^ ]+)',
1,
1,
null,
4
) itm_des
from thread_shades
/
ITM_COD ITM_DES
---------- --------
80064186 7921
80064187 7921
80114482 C-8762
80130541 C8676
SQL>
SY.
|
|
|
|
|
|
] 
Members
Search
Help
Register
Login
Home
