oracle query

hi,
can  you please advise how can i get the number part from the following string.

value

 #1(4):1589

i want to extract and check in where condition only the number that 1589 after :
thanks.
Sarma VadlamaniprogrammeranalystAsked:
Who is Participating?
 
awking00Commented:
Did you try the other way around?
and to_number(regexp_substr(SQL_BIND,'[0-9]+$'))=NVL(TO_NUMBER(:P_emp_no),emp.empno)
0
 
johnsoneSenior Oracle DBACommented:
Are you looking for everything after the colon?  That would be:

substr(your_column, instr(your_column, ':')+1)

Or, do you need to check that it is all numbers?
0
 
sdstuberCommented:
is the colon variable?  that is, could it be some other delimiter?
if the number you want is always the end of the string then try this...

regexp_substr(your_string,'[0-9]+$')
0
 
Sarma VadlamaniprogrammeranalystAuthor Commented:
and regexp_substr(SQL_BIND,'[0-9]+$')=NVL(TO_NUMBER(:P_emp_no),emp.empno)
passing as parameter and trying where clause not returning any rows
but if i enter

regexp_substr(SQL_BIND,'[0-9]+$')='1001' then returning rows of empno 1001.
tried to convert to char but no use.
please advise.
0
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.

All Courses

From novice to tech pro — start learning today.