Solved

Space Delimited Sql File

Posted on 2016-08-31
4
85 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
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.

776 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