Solved

Oracle SQL - formating a returned string from a query

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

Accepted Solution

by:
Stefan Hoffmann 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
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 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

707 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

20 Experts available now in Live!

Get 1:1 Help Now