Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to identify a linefeed character in a string

John O'TooleMay 24 2007 — edited May 24 2007

I have a table with some data in a VARCHAR2 column that contains linefeed characters.
These were entered by users pressing 'return' when in a web text box.
I want to get rid of these using the REPLACE function, but I'm having trouble identifying them.
If I have the following sample data:

CREATE TABLE test (text VARCHAR2(100));

INSERT INTO test VALUES ('Sample Text one line');
INSERT INTO test VALUES (
'Sample Text '||chr(10)||
'two lines');
INSERT INTO test VALUES (
'Sample '||chr(10)||
'Text  '||chr(10)||
'three lines');

SELECT * FROM test;

Then I'd like to identify them using something like...

SELECT text FROM test WHERE text LIKE '%CHR(10)%';

but that obviously doesn't work.
How can I find them?

Thanks.

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 21 2007
Added on May 24 2007
5 comments
326 views