Solved

Oracle SQL - formating a returned string from a query

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

Suggested Solutions

Title # Comments Views Activity
sql query help 15 52
Update the value of table B with value of table A tables inner join 6 75
SQL to JSON 14 32
Error in sql query statment. 21 44
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
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 video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

756 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