Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Oracle SQL - formating a returned string from a query

Posted on 2014-09-09
3
517 Views
Last Modified: 2014-09-09
I'm doing a number of queries that return a result string. These strings are placed in a text file and then emailed via a PowerBasic exe.  The queries retrun multiple "records", each within a RESULT_STRING that is placed in an array.

A simple example of the result strings piece of the query would look like:
B.PRODUCT_NAME || '* (#'||TO_CHAR(A.PRODUCT_ID) ||')' AS RESULT_STRING

Open in new window


The width of the product name varies and the max length is unknown at the time.
I would like to format the string within the SQL if possible so that it would be set up in columns.  The result would look like:
PRODUCT1          10234
PROD2                  31323
MYPRODUCT              16
.....

Open in new window


Is this possible within SQL ?
My other alternative is to handle it within the calling program, but I was hoping I could do it in the query.
0
Comment
Question by:GNOVAK
3 Comments
 
LVL 33

Accepted Solution

by:
ste5an earned 250 total points
ID: 40312488
This is normally better done in the front-end..
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 250 total points
ID: 40312632
the width might be unknown but hopefully something you can take a guess at and simply pick something big enough

user RPAD to create columns at fixed positions.    For example:

select rpad(col1,100) || rpad(col2,20) || rpad(col3,50) as result_string from yourtable

Note, the total concatenation must still be less than or equal to 4000 bytes
0
 

Author Closing Comment

by:GNOVAK
ID: 40313011
Thanks - I thought there might be some magic I'm missing and just wanted to make sure. Appreciate the help.
0

Featured Post

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Input a SQl, output tables amd columns used in the SQL 19 55
Read XML values 8 42
Oracle cursor lifecycle inside procedure. 2 13
Select question from MySQL 1 13
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

860 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