Home » SQL & PL/SQL » SQL & PL/SQL » Trim & Instr (Oracle 10g Rel 2 )
Trim & Instr [message #381186] Thu, 15 January 2009 10:15 Go to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Hi,

I have data in one of the columns as
SITE_ID=103,DEP_NB=32687,PASS2_DT=01/15/2009
SITE_ID=103,DEP_NB=32680,PASS2_DT=01/15/2009
SITE_ID=103,DEP_NB=32669,PASS2_DT=01/15/2009
SITE_ID=103,DEP_NB=32660,PASS2_DT=01/14/2009
SITE_ID=103,DEP_NB=32645,PASS2_DT=01/14/2009
SITE_ID=103,DEP_NB=32642,PASS2_DT=01/14/2009
SITE_ID=103,DEP_NB=32636,PASS2_DT=01/14/2009
SITE_ID=103,DEP_NB=32629,PASS2_DT=01/14/2009
SITE_ID=103,DEP_NB=32614,PASS2_DT=01/14/2009
SITE_ID=103,DEP_NB=32607,PASS2_DT=01/14/2009
SITE_ID=103,DEP_NB=32598,PASS2_DT=01/13/2009
SITE_ID=103,DEP_NB=32591,PASS2_DT=01/13/2009
SITE_ID=103,DEP_NB=32546,PASS2_DT=01/12/2009
SITE_ID=103,DEP_NB=32538,PASS2_DT=01/12/2009
SITE_ID=103,DEP_NB=32532,PASS2_DT=01/12/2009
SITE_ID=103,DEP_NB=32521,PASS2_DT=01/12/2009
SITE_ID=103,DEP_NB=32465,PASS2_DT=01/10/2009
SITE_ID=103,DEP_NB=32456,PASS2_DT=01/10/2009
SITE_ID=103,DEP_NB=32454,PASS2_DT=01/10/2009
SITE_ID=103,DEP_NB=32429,PASS2_DT=01/09/2009
SITE_ID=103,DEP_NB=32419,PASS2_DT=01/09/2009
SITE_ID=103,DEP_NB=32412,PASS2_DT=01/09/2009
SITE_ID=103,DEP_NB=32388,PASS2_DT=01/08/2009
SITE_ID=103,DEP_NB=32376,PASS2_DT=01/08/2009
SITE_ID=103,DEP_NB=32331,PASS2_DT=01/07/2009
SITE_ID=103,DEP_NB=32315,PASS2_DT=01/07/2009
SITE_ID=103,DEP_NB=32280,PASS2_DT=01/06/2009
SITE_ID=103,DEP_NB=32264,PASS2_DT=01/06/2009
SITE_ID=103,DEP_NB=32236,PASS2_DT=01/05/2009
SITE_ID=103,DEP_NB=32221,PASS2_DT=01/05/2009


these are different rows in the table,

Now i want to select data (only numbers )from this column basically between First , and second ,that is DEP_NB=32687 , I want only the numbers of dep_nb=

I tried with substr but since the data length is not fixed, i don't know what point dep_nb= will come, so i can't use substr,

Is there a way i can use substr and instr to pick up the data(only numbers) after DEP_NB= till the ,


Thanks
Re: Trim & Instr [message #381192 is a reply to message #381186] Thu, 15 January 2009 10:31 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Check this link.

http://www.orafaq.com/forum/m/353457/94420/?srch=instr#msg_353457

Regards

Raj
Re: Trim & Instr [message #381398 is a reply to message #381186] Fri, 16 January 2009 11:09 Go to previous messageGo to next message
mamalik
Messages: 266
Registered: November 2008
Location: Pakistan
Senior Member

Dear Try This.

SELECT SUBSTR (SUBSTR (col_nam, INSTR (col_nam, ',') + 1),
               INSTR (SUBSTR (col_nam, INSTR (col_nam, ',') + 1), '=') + 1,
                 INSTR (SUBSTR (SUBSTR (col_nam, INSTR (col_nam, ',') + 1),
                                  INSTR (SUBSTR (col_nam,
                                                 INSTR (col_nam, ',') + 1
                                                ),
                                         '='
                                        )
                                + 1
                               ),
                        ','
                       )
               - 1
              )
  FROM Tbl_Nam;


Best Regards.
Muhammad Asif Malik.

[Updated on: Fri, 16 January 2009 11:10]

Report message to a moderator

Re: Trim & Instr [message #381406 is a reply to message #381398] Fri, 16 January 2009 13:00 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
That's a little more involved than necessary.

Couple options could be:

With all versions:

select substr(col_nam, instr(col_nam, '=', 1, 2)+1, 
	instr(col_nam, ',', -1) - instr(col_nam, '=', 1, 2)-1) from tbl_nam;

With 10g+:

select regexp_substr(col_nam, '[0-9]+', 1, 2) from tbl_nam;
Previous Topic: Invalid Identifier
Next Topic: BULK COLLECT
Goto Forum:
  


Current Time: Thu Dec 08 16:24:37 CST 2016

Total time taken to generate the page: 0.05796 seconds