Oracle

substr help

Please support our Oracle advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

Thread Solved

•

Join Date: Feb 2008

Posts: 6

Reputation: mamtha is an unknown quantity at this point

Solved Threads: 0

mamtha

Offline

Newbie Poster

substr help

Aug 12th, 2009

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

 Help with Code Tags 
 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.

•

Join Date: Feb 2007

Posts: 2,104

Reputation: debasisdas will become famous soon enough

Solved Threads: 128

debasisdas debasisdas is offline

Offline

Postaholic

Re: substr help

Aug 13th, 2009

Try the following sample code.

 Help with Code Tags 
 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;