Solved

SQL query syntax with replace and concatenate

Posted on 2012-03-27
4
403 Views
Last Modified: 2012-03-27
Looking for help with query that spools data to ASCII file from table (tab1) with several  columns that have CR-s. If data doesn't have CR-s the following works:

spool tab1_data.out
select col1|| '|' ||
col2 || '|' ||
col3 || '|' ||
col4 || '|' ||
col5 || '|' ||
col6 from tab1 ;
spool off

However for two columns have to use

select replace (col3CHR(10),CHR(32))
select replace (col4CHR(10),CHR(32))

to replace CR with 'space'. - How to combine 'replace' functions and select-concatenate command?

Thank you Experts!
Regards
0
Comment
Question by:sevior
  • 2
4 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 300 total points
ID: 37772820
select col1|| '|' ||
col2 || '|' ||
replace (col3CHR(10),CHR(32))|| '|' ||
replace (col4CHR(10),CHR(32))|| '|' ||
col5 || '|' ||
col6 from tab1 ;
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 200 total points
ID: 37772823
Just use the function?


select col1|| '|' ||
col2 || '|' ||
replace (col3, CHR(10),CHR(32))|| '|' ||
replace (col4, CHR(10),CHR(32))|| '|' ||
col5 || '|' ||
col6 from tab1 ;
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 300 total points
ID: 37772827
another option (if appropriate i.e. none of the other fields have new-lines in them)

simply execute the replace on the entire concatenation

select replace(col1|| '|' ||
col2 || '|' ||
col3 || '|' ||
col4 || '|' ||
col5 || '|' ||
col6,chr(10),chr(32)) from tab1 ;
0
 

Author Closing Comment

by:sevior
ID: 37772991
Thank you!<br />That worked well.
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to recover a database from a user managed backup

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question