Solved

Oracle SQL - formating a returned string from a query

Posted on 2014-09-09
3
502 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 73

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How can I exclude some wording in a like statement? 39 67
Consolidating oracle query results to a single line 8 53
Remove alpha from alphanumeric 4 60
grouping logic 6 49
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
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…

863 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now