oracle 9i sql question

June 3, 2009 at 16:14:43
Specs: Windows XP, na
hi all,
have a sql question. i have a table which has a column varchar2(10) which holds employee numbers e.g 'X1234'. I need to update these and change them to lower case 'x' whereever they are uppercase 'X' e.g if number in column is 'X1234' i need to update it to be 'x1234'.

i dont want to update one at a time so is there an easy way to update them all in a sql update statement ??

thanks in advance.

See More: oracle 9i sql question

Report •

June 3, 2009 at 17:20:26
You don't want to update one at a time?
It's impossiable,for the database will change it one by one wahtever the sql is.

Report •

June 3, 2009 at 18:06:52
what i meant was i dont want to hard code each 'x number ' line by line. i dont really care how the db does it i just want the sql for it.

Report •

June 3, 2009 at 18:33:47
Try using a the lower case function and and a substring function if you have to restrict the scope.

Report •

Related Solutions

June 3, 2009 at 19:41:05
ok heres what i got so far :

update employee set number = (select lower(number) from employee where number like 'X%')

and this doesnt work since the subselect returns multiple rows.

so can someone suggest another way of doing this ??

Report •

July 30, 2010 at 07:53:44
I think my query is one of the solution for ur question. try it once.
but it surely works in oracle, i dnt knw abt the other

SET number = REPLACE(number,'X','x')

Report •

September 9, 2010 at 11:14:25
obviously it can done,
using this, try it out.

update xx
set name=lower(substr(name,1,1))||substr(name,-3);

Report •

September 9, 2010 at 11:21:27
SET number = REPLACE(number,'X','x')

dis is also correct but if x is occurs more than one den all x will be replaced.
so according to this view, dis might not fully satisfy d condition

Report •

Ask Question