ORACLE query

hi I have
a requirement to get the value from the query as follows
i want to get only the 2nd parameter value ie., only 2390
the below data i got from dba audit table when the row inserted.


  #2(0):  #3(4):2390 #4(10):Auditteset #5(6):Auditt #6(14):1/1/1969 0:0:0 #7(1):4 #8(1):N #9(1):N #10(1):N #11(1):N #12(1):2 #13(1):6 #14(1):1 #15(0):  #16(0):  #17(0):  #18(0):  #19(0):  #20(0):  #21(0):  #22(0):  #23(0):  #24(0):  #25(0):  #26(0):  #27(0):  #28(0):  #29(0):  #30(0):  #31(0):  #32(0):  #33(0):  #34(0):  #35(0):  #36(0):  #37(0):  #38(0):  #39(0):  #40(0):  #41(0):  #42(0):  #43(0):  #44(0):  #45(0):  #46(0):  #47(0):  #48(0):  #49(0):  #50(0):  #51(0):  #52(0):


similary
when updated only the 'AAAYeBAAGAAAJpdAAH ' should be retrieved.
can you please advise.

 #1(1):4 #2(18):AAAYeBAAGAAAJpdAAH #3(0):  #4(0):  #5(0):  #6(0):  #7(0):  #8(0):  #9(0):  #10(0):  #11(0):  #12(0):  #13(0):  #14(0):  #15(0):  #16(0):  #17(0):  #18(0):  #19(0):  #20(0):  #21(0):  #22(0):  #23(0):  #24(0):  #25(0):  #26(0):  #27(0):  #28(0):  #29(0):  #30(0):  #31(0):  #32(0):  #33(0):  #34(0):  #35(0):  #36(0):  #37(0):  #38(0):  #39(0):  #40(0):
Sarma VadlamaniprogrammeranalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark BullockQA Engineer IIICommented:
If your column is named sarma and your table named test:
substr(substr(substr(sarma, instr(sarma, ':')+1), instr(substr(sarma, instr(sarma, ':')+1), ':')+1)
         , 1, instr(substr(substr(sarma, instr(sarma, ':')+1), instr(substr(sarma, instr(sarma, ':')+1), ':')+1), ' '))

Open in new window

0
Mark BullockQA Engineer IIICommented:
If you want to see how I built the query and tested it:
http://sqlfiddle.com/#!4/5955b/7
0
johnsoneSenior Oracle DBACommented:
The provided example can be simplified to this:
SELECT Substr(sarma, Instr(sarma, ':', 1, 2) + 1, 
              Instr(sarma, ' ', Instr(sarma, ':', 1 
                                , 2) + 1) - Instr(sarma, ':', 1, 2) - 1) 
FROM   test; 

Open in new window

The INSTR function can find the second occurrence (or nth occurrence) of a character.  You don't need to jump through all those hoops to find the second one.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Sarma VadlamaniprogrammeranalystAuthor Commented:
can you please send me the query
thanks. i tried i got the first occurrance  but could not get the second.
0
johnsoneSenior Oracle DBACommented:
I am confused.  2 different queries were posted.  Which one is not working and can you provide sample data that shows it is not working.

This is the full test case that I used:
SQL> CREATE TABLE test
  2    (
  3       sarma VARCHAR2(1000)
  4    );

Table created.

SQL> INSERT INTO test
  2              (sarma)
  3  VALUES      (
  4  '  #2(0):  #3(4):2390 #4(10):Auditteset #5(6):Auditt #6(14):1/1/1969 0:0:0 #7(1):4 #8(1):N #9(1):N #10(1):N #11(1):N #12(1):2 #13(1):6 #14(1):1 #15(0):  #16(0):  #17(0):  #18(0):  #19(0):  #20(0):  #21(0):  #22(0):  #23(0):  #24(0):  #25(0):  #26(0):  #27(0):  #28(0):  #29(0):  #30(0):  #31(0):  #32(0):  #33(0):  #34(0):  #35(0):  #36(0):  #37(0):  #38(0):  #39(0):  #40(0):  #41(0):  #42(0):  #43(0):  #44(0):  #45(0):  #46(0):  #47(0):  #48(0):  #49(0):  #50(0):  #51(0):  #52(0):'
  5  );

1 row created.

SQL> INSERT INTO test
  2              (sarma)
  3  VALUES      (
  4  ' #1(1):4 #2(18):AAAYeBAAGAAAJpdAAH #3(0):  #4(0):  #5(0):  #6(0):  #7(0):  #8(0):  #9(0):  #10(0):  #11(0):  #12(0):  #13(0):  #14(0):  #15(0):  #16(0):  #17(0):  #18(0):  #19(0):  #20(0):  #21(0):  #22(0):  #23(0):  #24(0):  #25(0):  #26(0):  #27(0):  #28(0):  #29(0):  #30(0):  #31(0):  #32(0):  #33(0):  #34(0):  #35(0):  #36(0):  #37(0):  #38(0):  #39(0):  #40(0):'
  5  );

1 row created.

SQL> commit;

Commit complete.

SQL> SELECT Substr(sarma, Instr(sarma, ':', 1, 2) + 1,
  2                Instr(sarma, ' ', Instr(sarma, ':', 1
  3                                  , 2) + 1) - Instr(sarma, ':', 1, 2) - 1)
  4  FROM   test;

SUBSTR(SARMA,INSTR(SARMA,':',1,2)+1,INSTR(SARMA,'',INSTR(SARMA,':',1,2)+1)-INSTR
--------------------------------------------------------------------------------
2390
AAAYeBAAGAAAJpdAAH

Open in new window

From what I see, that is pulling the 2 values that you requested.
0
Sarma VadlamaniprogrammeranalystAuthor Commented:
Thanks but these string is coming from one table sql bind
from dba_audit table.
but problem is when I update the record in table it is capturing rowid
when i insert and select then client ID will come
so the data in all row is consistent.

how can I validate the different type of data can you please advise.
0
Sarma VadlamaniprogrammeranalystAuthor Commented:
Ok i think i am not clear
my requirement is i am getting SQL BIND from dba_common_audit_trail and would like to use the the value in where clause
for eg
#1(4):1491 #2(14):1/1/2015 0:0:0 #3(14):6/1/2015 0:0:0 #4(2):NA #5(1):N #6(1):N #7(1):N #8(2):DR #9(3):PSR #10(2):FL #11(0):  #12(1):N #13(1):1 #14(2):13 #15(2):29 #16(2):58 #17(3):302 #18(3):343 #19(1):N #20(0):  #21(0):  #22(0):  #23(0):  #24(0):  #25(0):  #26(0):  #27(0):  #28(0):  #29(0):  #30(0):  #31(0):  #32(0):  #33(0):  #34(0):  #35(0):  #36(0):  #37(0):  #38(0):  #39(0):  #40(0):  #41(0):  #42(0):  #43(0):  #44(0):  #45(0):  #46(0):  #47(0):  #48(0):  #49(0):  #50(0):  #51(0):  #52(0):  #53(0):  #54(0):  #55(0):  #56(0):

one of the row. and would like to extract only nth value and replace remaining string with null like
in the above string iwant the parameter  " 13 "
 (#14(2):13 #) and all remaing string should be null
0
johnsoneSenior Oracle DBACommented:
Given that as the input, what would the output be.

Also, what would the output be if this was the input?

#1(4):1491 #2(14):1/1/2015 0:0:0 #3(14):6/1/2015 0:0:0 #4(2):NA #5(1):N #6(1):N #7(1):N #8(2):DR #9(3):PSR #10(2):FL #11(0):  #12(1):N #13(1):1 #14(2):13 #15(2):13 #16(2):58 #17(3):302 #18(3):13 #19(1):N #20(0):  #21(0):  #22(0):  #23(0):  #24(0):  #25(0):  #26(0):  #27(0):  #28(0):  #29(0):  #30(0):  #31(0):  #32(0):  #33(0):  #34(0):  #35(0):  #36(0):  #37(0):  #38(0):  #39(0):  #40(0):  #41(0):  #42(0):  #43(0):  #44(0):  #45(0):  #46(0):  #47(0):  #48(0):  #49(0):  #50(0):  #51(0):  #52(0):  #53(0):  #54(0):  #55(0):  #56(0):

Notice that multiple values are 13.

Does this have to be done in a SQL statement or can you use a function?
0
Sarma VadlamaniprogrammeranalystAuthor Commented:
function or query is fine

i need  nth value after the " : " 
from the string
 #1(4):1491 this is first paramter and the value of the first paramter is  1491

#1(4):1491
parameter 14 is   #14(2):13

so if i enter 14 th occurance i need 13
if i enter 1st occurance it should be 1491
0
johnsoneSenior Oracle DBACommented:
Change the bold 2 in the following to whatever occurrence you want.

SELECT Substr(sarma, Instr(sarma, ':', 1, 2) + 1,
              Instr(sarma, ' ', Instr(sarma, ':', 1
                                , 2) + 1) - Instr(sarma, ':', 1, 2) - 1)
FROM   test;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Sarma VadlamaniprogrammeranalystAuthor Commented:
this is not working as there is data problem thanks for the response
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.