Link to home
Start Free TrialLog in
Avatar of chongchian
chongchianFlag for Singapore

asked on

Getting ROWCount and Replace function in Oracle SQL

Hi All,

I'm trying to do something like this in Oracle10g. Not sure how to do it, since all solutions I found on Google were with PL/SQL.

SELECT %ROWCOUNT, ID
FROM TableName

I would also like to do the following, but could not seem to find a SQL equiv. Only PL/SQL.

UPDATE TableName
SET COLNAME = COLNAME.Replace(",",".")

Appreciate any advice.
Avatar of Sean Stuber
Sean Stuber

very close...


I assume you want to replace all commas with periods,  if so, try this.


UPDATE TableName
SET COLNAME = Replace(COLNAME,",",".")
for the rowcount I don't know what you're trying to do.


do you want a rownumber for each row? if so


select rownum, id from yourtable;


if you want a count of the number of rows in the entire table :


select count(*) over(), id from yourtable
Avatar of chongchian

ASKER

thanks sdstuber. What i was trying to do is to list the index of the row which is different from the ID. Thanks lots for your kind advice.
by "index" if you mean the number of each row,  that's not deterministic without an order by
hmm... what if i wanted to also display the row index?
Example, Table consists of the following

----   ----------------
ID      Name
----   ----------------
av1   John
av2   James
av9   Jason
av4   Tom
av3   Joe

to be displayed as

----   ----   ----------------
S/N   ID      Name
----   ----   ----------------
1      av1   John
2      av2   James
3      av9   Jason
4      av4   Tom
5      av3   Joe
ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial