Home » SQL & PL/SQL » SQL & PL/SQL » finding entries with an return on the end (10.2.0.3.0)
finding entries with an return on the end [message #414746] Thu, 23 July 2009 06:38 Go to next message
dior
Messages: 25
Registered: April 2009
Junior Member
Hi,

I´ve a table with enties in one column where some of them ending with a "Return".

This is because during the implementation of the data, the on put the data in the DB hit the return key.

So I´ve a column with entries consisting of 7 digits (e.g. 3057456) and some of them have a return char at the end.

How can i search for the entries with this return char?
And then, how I delete the return?

thx in advance

[Updated on: Thu, 23 July 2009 06:47]

Report message to a moderator

Re: finding entries with an return on the end [message #414750 is a reply to message #414746] Thu, 23 July 2009 06:48 Go to previous messageGo to next message
pointers
Messages: 410
Registered: May 2008
Senior Member
use 'like' operator

Re: finding entries with an return on the end [message #414757 is a reply to message #414750] Thu, 23 July 2009 07:07 Go to previous messageGo to next message
janseni2002
Messages: 1
Registered: July 2009
Location: Netherlands
Junior Member

You can use something like this:
 col1 like '%'||chr(10)

[Updated on: Thu, 23 July 2009 07:11]

Report message to a moderator

Re: finding entries with an return on the end [message #414764 is a reply to message #414757] Thu, 23 July 2009 07:33 Go to previous messageGo to next message
dior
Messages: 25
Registered: April 2009
Junior Member
This works, thx... but could you explain this to me a little bit please!

when i use '%'||chr(10) i get the entries i need but when i change the (10) to a lower or higher number '%'||chr(7) ....
or '%'||chr(11) i get nothing.

Re: finding entries with an return on the end [message #414775 is a reply to message #414764] Thu, 23 July 2009 07:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
10 is code point for "Return".

Regards
Michel
Re: finding entries with an return on the end [message #415034 is a reply to message #414746] Fri, 24 July 2009 07:57 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
replace the return with comma,space,Null whatever you like.
Re: finding entries with an return on the end [message #415080 is a reply to message #414746] Fri, 24 July 2009 11:52 Go to previous message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
update table
set col1 = replace(col1,chr(10))
where instr(col1,chr(10)) > 0;
Previous Topic: Record insertion time
Next Topic: Use index
Goto Forum:
  


Current Time: Fri Dec 09 05:58:47 CST 2016

Total time taken to generate the page: 0.05728 seconds