Ad:

Oracle Discussion Thread
Marked Solved
Views: 867

Aug 12th, 2009

substr help

Expand Post »

I need to derive last 2 numbers after the '-' from the code column.
example if the code is 76767-99 then I need a query to return only 99. I am using substr and instr functions but not getting what I want. Please help me correct the below query.
code - 76767-99

sql Syntax (Toggle Plain Text)
SELECT NVL (TRIM (SUBSTR (code,
                                             (INSTR (code,
                                                     '_'
                                                    )
                                             )
                                           + 1
                                          )
                                  ),
                             '0'
                            ) FROM temp_table;
select NVL (TRIM (SUBSTR (code,
                                             (INSTR (code,
                                                     '_'
                                                    )
                                             )
                                           + 1
                                          )
                                  ),
                             '0'
                            ) from temp_table;

Thanks,

Last edited by peter_budo; Aug 13th, 2009 at 12:39 pm. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks) and [icode] (inline code) tags.

Similar Threads

strings as input, substr and len in C++
Fast substr in C++
substr problem in C++
atoi(string.substr(x,x)) Possible? in C++
An equivalent method of substr() in C in C++

mamtha

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

7 posts
since Feb 2008

Permalink

Aug 13th, 2009

Re: substr help

Try the following sample code.

Oracle Syntax (Toggle Plain Text)
SELECT code,SUBSTR(code,INSTR(code,'-',1)+1,2) FROM table_name;
SELECT code,substr(code,instr(code,'-',1)+1,2) from table_name;

debasisdas

Featured Poster

Reputation Points: 665

Solved Threads: 427

Posting Genius

Offline

6,406 posts
since Feb 2007

Permalink

Aug 13th, 2009

Re: substr help

Thanks for your help

mamtha

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

7 posts
since Feb 2008

Permalink

Aug 14th, 2009

Re: substr help

You are most welcome.

Last edited by debasisdas; Aug 14th, 2009 at 5:54 pm.

debasisdas

Featured Poster

Reputation Points: 665

Solved Threads: 427

Posting Genius

Offline

6,406 posts
since Feb 2007

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in Oracle Forum Timeline: Simple

Next Thread in Oracle Forum Timeline: oracle help

DaniWeb Message

Cancel Changes

User Name
Password