Home » SQL & PL/SQL » SQL & PL/SQL » Trim function on table
Trim function on table [message #299316] Mon, 11 February 2008 07:12 Go to next message
ashish2345
Messages: 50
Registered: September 2007
Member
Hi,

I have created a table by reading from a flat file.
Some of the entries in file ended by '|' which has got saved in table.

The entries in table are
C1 C2 C3
1 fIRST|
2 Second|

Can anyone tell how to trim(|) in the whole second column of the above table

thanks a lot
Re: Trim function on table [message #299318 is a reply to message #299316] Mon, 11 February 2008 07:17 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
use update with "replace" funcion.

Here is the demonstration

Desc b
a number;
c varchar2(10);

select * from b
A B
1 first|
1 second|

and the required update statement is

update b set c = replace(c,'|')

[Updated on: Mon, 11 February 2008 07:22]

Report message to a moderator

Re: Trim function on table [message #299320 is a reply to message #299318] Mon, 11 February 2008 07:29 Go to previous messageGo to next message
ashish2345
Messages: 50
Registered: September 2007
Member
Thanks a lot Raman,

I have made a dummy table from this xtrnal one .The statement works fine

Many thanks

Re: Trim function on table [message #299321 is a reply to message #299318] Mon, 11 February 2008 07:29 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
If you also have "|"s in the string itself that you want to keep, another option is just to trim any trailing "|"s.
update b set c = trim( trailing '|' from c );
Re: Trim function on table [message #299322 is a reply to message #299321] Mon, 11 February 2008 07:32 Go to previous messageGo to next message
ashish2345
Messages: 50
Registered: September 2007
Member
Thanks a lot Thomas ....

Best regards
Re: Trim function on table [message #299323 is a reply to message #299318] Mon, 11 February 2008 07:33 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@ramanajv1968,

Don't put solution only hint or clue as requested in OraFAQ Forum Guide, "Responding to Posts" section:
Quote:
When responding to questions, if it is obviously a student with a homework assignment or someone else just learning, especially in the homework and newbies forums, it is usually best to provide hints or clues, perhaps links to relevant portions of the documentation, or a similar example, to point them in the right direction so that they will research and experiment on their own and learn, rather than providing complete solutions to problems. In cases where someone has a real-life complex work problem, or at least it may seem complex to them, it may be best to provide a complete demo and explanation.

Regards
Michel
Previous Topic: How to aggregate similar rows into one
Next Topic: SQL to find chained item groups
Goto Forum:
  


Current Time: Thu Dec 08 16:05:28 CST 2016

Total time taken to generate the page: 0.11844 seconds