How to query up the newline (||) character in SQL editor

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jason Ip - 21 Apr 2005 20:30 GMT

I have a bunch of comments rows with just plain text in them but some
of them have a newline character in them (||). I need to replace all
instances of these newlines (in a certain row) with a blank space, or
anything for that matter. I just need to get rid of them.

Right now I am just trying to query them out like so:

select * from certain_tables where comment like '%(need identifier
here)%';

what do i put for that identifier to get the newline characters?

thanks

Reply to this Message

Art S. Kagel - 21 Apr 2005 22:34 GMT

> I have a bunch of comments rows with just plain text in them but some
> of them have a newline character in them (||). I need to replace all
[quoted text clipped - 7 lines]
>
> what do i put for that identifier to get the newline characters?

You cannot. You'll have to write a host language program in ESQL/C, C-ODBC,
Perl/DBD/DBI, Java/JDBC, etc. and filter, and correct the data in the host
program then update the offending rows.

Art S. Kagel

> thanks

Reply to this Message

scottishpoet - 22 Apr 2005 11:52 GMT

think you'll need to match every char[i] in your string against the
ASCII representation of a newline

If the ascii representation is newline, the replace that character with
a space.
if you have replaced any characters then update that row,

you should be able to write a stored procedure to do it.

Reply to this Message

Doug Lawry - 22 Apr 2005 14:32 GMT

Not so, Art. You can use the built-in procedure IFX_ALLOW_NEWLINE, demonstrated
as follows:

CREATE TEMP TABLE test_table (test_column VARCHAR(255));

EXECUTE PROCEDURE IFX_ALLOW_NEWLINE('T');

INSERT INTO test_table VALUES ('a
b');

SELECT * FROM test_table;

UPDATE test_table
SET test_column = REPLACE(test_column, '
', ' ')
WHERE test_column LIKE '%
%';

SELECT * FROM test_table;

Jason: use the REPLACE function in an UPDATE statement as shown above to fix
your data.

Signature

Regards,
Doug Lawry
www.douglawry.webhop.org

>> I have a bunch of comments rows with just plain text in them but some
>> of them have a newline character in them (||). I need to replace all
[quoted text clipped - 14 lines]
>
>> thanks

Reply to this Message

Paul Watson - 28 Apr 2005 20:00 GMT

I haven't tested but I would expect the regexp datablade to do this as well

> Not so, Art. You can use the built-in procedure IFX_ALLOW_NEWLINE, demonstrated
> as follows:
[quoted text clipped - 18 lines]
> Jason: use the REPLACE function in an UPDATE statement as shown above to fix
> your data.

Signature

Paul Watson #
Oninit Ltd # Growing old is mandatory
Tel: +44 1436 672201 # Growing up is optional
Fax: +44 1436 678693 #
Mob: +44 7818 003457 #
www.oninit.com #

Reply to this Message