Solved

SQL query syntax with replace and concatenate

Posted on 2012-03-27
4
399 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 76

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

809 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