DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: SQL query

  1. #1
    Join Date
    Mar 2004
    Location
    DC,USA
    Posts
    650

    SQL query

    I have a column A which is populated by application appending a unique number to the col value. Not all the records has the number. I need to get all the values of the changed column A(with numbers) to get the associated values of the column B in the result set. Table has 1 million rows.

    Code:
    COL A
    Management group app
    Financial group app# 980543
    Management group app# 567345
    Financial group app
    "What is past is PROLOGUE"

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Code:
    SQL> select * from mytable ;
    
    SOME
    ------------------------------
    Management group app
    Financial group app# 980543
    Management group app# 567345
    Financial group app
    
    SQL> get x
      1  select some, substr(some, instr(some,' ',-1)+1) num
      2* from mytable where instr(some,'#') > 0
    SQL> /
    
    SOME                           NUM
    ------------------------------ -------------
    Financial group app# 980543    980543
    Management group app# 567345   567345
    Tamil

  3. #3
    Join Date
    Mar 2004
    Location
    DC,USA
    Posts
    650
    Works great!

    Thanks Tamil
    "What is past is PROLOGUE"

  4. #4
    Join Date
    Apr 2006
    Posts
    377
    This will work for you in 10g.

    Code:
    select regexp_substr(col_a,'[[:digit:]]+$') from test_exp
    where regexp_instr(col_a,'#') > 0;
    
    or
    
    select regexp_replace(col_a,'(.*# )','\2') from test_exp
    where regexp_instr(col_a,'#') > 0;
    Last edited by ebrian; 07-27-2006 at 03:08 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width