Home » SQL & PL/SQL » SQL & PL/SQL » How can i use Substr to get required result (Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit)
How can i use Substr to get required result [message #574150] Mon, 07 January 2013 07:54 Go to next message
mamalik
Messages: 256
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 Go to previous messageGo to next message
joy_division
Messages: 4559
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 Go to previous messageGo to next message
cookiemonster
Messages: 11285
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?
How can i get this using regular function reg_substr [message #574165 is a reply to message #574153] Mon, 07 January 2013 09:29 Go to previous messageGo to next message
mamalik
Messages: 256
Registered: November 2008
Location: Pakistan
Senior Member

yes, these are possible formats.
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 Go to previous messageGo to next message
_jum
Messages: 490
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: How can i get this using regular function reg_substr [message #574174 is a reply to message #574165] Mon, 07 January 2013 10:38 Go to previous messageGo to next message
Michel Cadot
Messages: 60012
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Always post your Oracle version, with 4 decimals.
2/ With any SQL or PL/SQL question, please, Post a working Test case: create table 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.

Regards
Michel
Re: Oracle 10g [message #574187 is a reply to message #574150] Mon, 07 January 2013 13:23 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2079
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 #574337 is a reply to message #574170] Wed, 09 January 2013 02:47 Go to previous messageGo to next message
sss111ind
Messages: 507
Registered: April 2012
Location: India
Senior Member

Hi All,

Is it possible by using regexp_substr.I am confused while using regexp_substr and regexp_replace

Regards,
Nathan
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2079
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.
Re: How can i get this using regular function reg_substr [message #574444 is a reply to message #574347] Thu, 10 January 2013 07:11 Go to previous message
sss111ind
Messages: 507
Registered: April 2012
Location: India
Senior Member


Thank You.It's very hard to understanding regexp.

Regards,
Nathan
Previous Topic: Generic application error
Next Topic: Extracting part of a string
Goto Forum:
  


Current Time: Sun Dec 21 14:16:26 CST 2014

Total time taken to generate the page: 0.11631 seconds