Solved

Space Delimited Sql File

Posted on 2016-08-31
4
81 Views
Last Modified: 2016-09-15
A vendor has asked me to create an extract file.
In this extract file, certain fields need to be in a particular position.
For example:
Employee Position 1-8
Employee First Name Position 9-26
Employee Middle Name Position 27-27
Employee Last Name Position 28-42

I was thinking about doing this in oracle sql, anyone have any suggestions on how to do this?
0
Comment
Question by:metalteck
  • 2
  • 2
4 Comments
 
LVL 35

Accepted Solution

by:
Mark Geerlings earned 500 total points
ID: 41778345
This is quite simple in Oracle SQL.  Simply concatenate the four column values together into one result column, and use "to_char" on the first column (assuming that is a numeric column) and use either "rpad" or "substr" (or maybe both) on the first and last name columns, plus possibly an "nvl" on the middle name column in case that is null (blank).

Something like this should work (assuming the actual column names are:
employee_id
first_name
middle_name
last_name)

select to_char(employee_id,'99999999')
||rpad(substr(first_name,1,17),17,' ')
||nvl(middle_name,' ')
||rpad(substr(last_name,1,15),15,' ') "Output"
from [your_table]
where ...
order by  ...

(The "where..." and "order by ..." clauses are optional.)

I think the lengths for first_name and last_name are correct.  But, you will have to test them.

If you use SQL*Plus to run this, you will want to use these commands before running the query:
set pagesize 0;
set linesize 80;
set trimspool off;
set head off;
set feedback off;
spool C:\temp\my_file.txt
 [your SQL query here]
spool off;

The "spool" command will open an output file, so change "\temp" to a valid directory on your system if necessary, and change "my_file.txt" to whatever you want the output file to be named.
1
 

Author Comment

by:metalteck
ID: 41791585
This is working for me. What is the code I would need to use for a date?
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 41791658
That depends on what format you want the "date" column value displayed in, and whether you want to include the time-of-day, or not.  By default, Oracle "date" columns include the time-of-day, as well as the date.  But, depending on the application, the time-of-day may or may not be included.  If it is not included by your application, that is the same as an actual time of exactly midnight.

For a date column, you would want to use the Oracle "to_char" operator and a format mask so Oracle puts the "date" value into characters as your vendor wants them to be displayed.

For example, if your "date" column is named "my_date" and the actual "date" value is Sept 9, 2016 1:13:45pm, you could any of these in your query, or something similar:

Query expression                                                 Result
--------------------------------------------------------------    --------------
to_char(my_date,'MM/DD/YYYY')                        09/09/2016
to_char(my_date,'YYYYMMDD')                           20160909
to_char(my_date,'Mon. Dy, YYYY')                       Sep. 9, 2016
to_char(my_date,'MM/DD/YYYY HH24:MI:SS')   09/09/2016 13:13:45

Note, there are dozens of other possible combinations or variations for format elements for "date" values in Oracle queries when you use "the "to_char" operator.  Check the Oracle documentation for "to_char" or "format mask" for a complete list.

You can concatenate this "to_char" column result just like you can with the other datatypes, something like this:
select to_char(employee_id,'99999999')
 ||rpad(substr(first_name,1,17),17,' ')
 ||nvl(middle_name,' ')
 ||rpad(substr(last_name,1,15),15,' ')
 ||to_char(my_date,'MM/DD/YYYY') "Output"
 from [your_table]
0
 

Author Comment

by:metalteck
ID: 41791738
Ok, I will try that.
And what is the syntax for blank spaces?
My extract has some fields that will be blank, and at various lengths.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to take different types of Oracle backups using RMAN.

910 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

23 Experts available now in Live!

Get 1:1 Help Now