weird '\n' on string Options

[igoticecream]

Hi guys, I'm new on this forum as well with the database world.

Let me explain the problem: I'm inserting a string ('Magic Kingdom')into a table through script editor of oracle 10g XE (the type of the column is varchar2). When i consult for that string, the output is 'Magic \nKingdom'. Now, if i insert the same but this time using SQL*Plus/Appex interface, i get 'Magic Kingdom', there's no '\n'... so I'm guessing the problem occurs because of the script.

After hours searching the reason why the '\n' (which is bothering me a lot) i was unable to find it and now I'm asking for a little help how i can fix it

[SteveC]

The backslash n usually represents a new line. What are you using (tool-wise) to "consult" for that string? Is the column width constrained, so what you are using wraps the second work onto a new line?

[igoticecream]

The backslash n usually represents a new line. What are you using (tool-wise) to "consult" for that string? Is the column width constrained, so what you are using wraps the second work onto a new line?

SQL*Plus, i get the following output:

Names:
---------
Magic
Kingdom
Epcot
Animal
Kingdom

The output i was expecting is:

Names:
---------
Magic Kingdom
Epcot
Animal Kingdom

Also, the interface I'm writting for the data base show me the '\n' if i debug it ("Magic \nKingdom"). This is happening with all string with white space

I don't get it, if i insert the exact same thing using SQL*Plus, theres no '\n'... only if i insert using the script

[burleson]

Hi,

>> remove the \n from my table rows

If you know C or C++, the '\n' is the carriage return character. SQL*PLus read this, and inserts a carriage return.

The "\n" is equivalent to chr(10), and you can insert this into a Oracle string:

select
col1||chr(10)||col2
from mytab;

In your case, you need to update your rows to remove the carriage returns. . . .

I would use regexp_replace to remove the |\n" from the rows:

http://dba-oracle.com/googlesearchsite_pro...=regecp_replace

REGEXP_REPLACE

Syntax: regexp_replace( source, pattern, replace string, position, occurrence, options)

The source can be a string literal, variable, or column. The pattern is the expression to be replaced. The replace string is the text that will replace the matching patterns. The optional position defines the location to begin searching the source string. This defaults to 1. The optional occurrence defines the occurrence of the pattern that you want replaced. This defaults to 0 (all occurrences). Setting this to a positive number will result in only that occurrence being replaced. The matching options are the same.

Something like this;

CODE

set escape '\'
create table
   mytab1
as
select  mycol = regexp_replace(my_column, '\n', '')
from mytab;

I have not tested this; you may need to "escape" the backslash:
http://www.dba-oracle.com/t_escaping_speci...sql_sqlplus.htm

Also see:

http://www.dba-oracle.com/t_remove_all_spe..._table_rows.htm

http://www.dba-oracle.com/t_change_all_string_table.htm

[igoticecream]

Hi,

>> remove the \n from my table rows

If you know C or C++, the '\n' is the carriage return character. SQL*PLus read this, and inserts a carriage return.

The "\n" is equivalent to chr(10), and you can insert this into a Oracle string:

select
col1||chr(10)||col2
from mytab;

In your case, you need to update your rows to remove the carriage returns. . . .

I would use regexp_replace to remove the |\n" from the rows:

http://dba-oracle.com/googlesearchsite_pro...=regecp_replace

REGEXP_REPLACE

Syntax: regexp_replace( source, pattern, replace string, position, occurrence, options)

The source can be a string literal, variable, or column. The pattern is the expression to be replaced. The replace string is the text that will replace the matching patterns. The optional position defines the location to begin searching the source string. This defaults to 1. The optional occurrence defines the occurrence of the pattern that you want replaced. This defaults to 0 (all occurrences). Setting this to a positive number will result in only that occurrence being replaced. The matching options are the same.

Something like this;

CODE

set escape '\'
create table
   mytab1
as
select  mycol = regexp_replace(my_column, '\n', '')
from mytab;

I have not tested this; you may need to "escape" the backslash:
http://www.dba-oracle.com/t_escaping_speci...sql_sqlplus.htm

Also see:

http://www.dba-oracle.com/t_remove_all_spe..._table_rows.htm

http://www.dba-oracle.com/t_change_all_string_table.htm

Thanks burleson that really helped, +rep