-
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"
-
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
-
Works great!
Thanks Tamil
"What is past is PROLOGUE"
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|