Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> weird '\n' on string
igoticecream
post Jan 6 2011, 09:06 PM
Post #1


Newbie
*

Group: Members
Posts: 3
Joined: 6-January 11
Member No.: 44,485



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 tongue.gif
Go to the top of the page
 
+Quote Post
SteveC
post Jan 6 2011, 10:47 PM
Post #2


Advanced Member
***

Group: Members
Posts: 2,835
Joined: 11-March 08
Member No.: 18,933



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?
Go to the top of the page
 
+Quote Post
igoticecream
post Jan 7 2011, 06:50 AM
Post #3


Newbie
*

Group: Members
Posts: 3
Joined: 6-January 11
Member No.: 44,485



QUOTE (SteveC @ Jan 6 2011, 10:47 PM) *
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 ohmy.gif

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
Go to the top of the page
 
+Quote Post
burleson
post Jan 7 2011, 09:03 AM
Post #4


Advanced Member
***

Group: Members
Posts: 11,328
Joined: 26-January 04
Member No.: 13



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



--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
igoticecream
post Jan 7 2011, 12:37 PM
Post #5


Newbie
*

Group: Members
Posts: 3
Joined: 6-January 11
Member No.: 44,485



QUOTE (burleson @ Jan 7 2011, 09:03 AM) *
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 rolleyes.gif
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 28th July 2014 - 05:30 PM