Home » SQL & PL/SQL » SQL & PL/SQL » update name (Oracle 9i)
update name [message #449085] Fri, 26 March 2010 09:47 Go to next message
ramesh55.sse
Messages: 262
Registered: December 2008
Location: Hyderabad
Senior Member
Hi All,

In the table the names are having diffrent spaces for one name only one space between the words,for another name two spaces between words,for other names three spaces between the words.
I want to update with single space how can we that.
Please help me on this scenario. I am giving the data as follows.

CREATE TABLE student(sname VARCHAR2(30));

INSERT INTO student VALUES('PRAKASH BABU');
INSERT INTO student VALUES('RAJESH    KUMAR');
INSERT INTO student VALUES('POORNA        CHANDAR');
INSERT INTO student VALUES('ANIL     RAJ');
INSERT INTO student VALUES('ABHI     RAM   KONA');
INSERT INTO student VALUES('SRI  TEJA        MEDI');


Like this the table contains millions of rows.

I want to update the names like this.

PRAKASH BABU
RAJESH KUMAR
POORNA CHANDAR
ANIL RAJ
ABHI RAM KONA
SRI TEJA MEDI

Re: update name [message #449091 is a reply to message #449085] Fri, 26 March 2010 10:27 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you were on 10g, you could just use REGEXP_REPLACE to replace any number of spaces with a single space.

As it is, you'll have to do something like

UPDATE student
SET sname = substr(sname,1,instr(sname,' ')) || substr(sname,-1*(length(sname) - instr(sname,' ',-1)));
Re: update name [message #449092 is a reply to message #449085] Fri, 26 March 2010 10:34 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
prakasha wrote on Fri, 26 March 2010 09:47

Like this the table contains millions of rows.


and you might not want to update millions row in single update query ( if it is production ).
Re: update name [message #449095 is a reply to message #449085] Fri, 26 March 2010 10:57 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You may be interested in the following topic on AskTom: Replacing Multiple Blanks by single blanks in text


Regards
Michel
Tell me and I'll forget; show me and I may remember; involve me and I'll understand
Re: update name [message #449179 is a reply to message #449085] Sat, 27 March 2010 11:32 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
Open the oracle sql manual and read about sql functions. In particular you will find on called REPLACE.

Kevin
Re: update name [message #449832 is a reply to message #449085] Thu, 01 April 2010 04:44 Go to previous messageGo to next message
sivaora
Messages: 119
Registered: October 2009
Location: Hyderabad
Senior Member
Hi, Try this to get the required output.

SQL> select * from student;

SNAME
------------------------------
PRAKASH BABU
RAJESH KUMAR
POORNA CHANDAR
ANIL RAJ
ABHI RAM KONA
SRI TEJA MEDI

6 rows selected.

SQL> update student set sname = replace(replace(replace(sname,' ',' s'),'s ',''),'s','');

6 rows updated.

SQL> commit;

Commit complete.

SQL> select * from student;

SNAME
------------------------------
PRAKASH BABU
RAJESH KUMAR
POORNA CHANDAR
ANIL RAJ
ABHI RAM KONA
SRI TEJA MEDI

6 rows selected.

-------------------------------
Regards
siva
Re: update name [message #449836 is a reply to message #449832] Thu, 01 April 2010 05:20 Go to previous message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

If you read the link I provided you will see why your solution does not work but for this very specific example (maybe as with no formatting we can't say if there is one or more blanks, maybe this is the solution: give the result in html).


Regards
Michel
Tell me and I'll forget; show me and I may remember; involve me and I'll understand
Previous Topic: Getting most common value
Next Topic: Count of records
Goto Forum:
  


Current Time: Wed Sep 28 05:45:17 CDT 2016

Total time taken to generate the page: 0.12693 seconds