SQL Question - setting a fixed record length using Spool

[OrangeHopper]

I've just done the following and it works.

SET LINESIZE 71
SET WRAP OFF

[TheFaQQer]

He is running against Oracle using sqlplus.

In that case, I'll stand by my original suggestion.

[OrangeHopper]

He is running against Oracle using sqlplus.

[TheFaQQer]

I'm trying to use the SQL spool command to set a max record length for the output file I'm spooling to. However, when the data exceeds the max record length, it just wraps the data onto the next record.

I'm looking for a way of stopping it writing the extra record, I just want it to truncate the data at the max record length. I can't see an obvious way to do this, and being new to SQL I can't work out the best way to do it. Any advice would be appreciated. Thanks.

For example:

CLEAR BREAKS
SET HEADING OFF
SET LINESIZE 71
SET ECHO OFF
SET SERVEROUTPUT OFF
SET TERMOUT OFF
SET FEEDBACK OFF
SET PAGESIZE 0

SPOOL D:\Data\outputfile.dat
SELECT *
FROM table_name;
SPOOL OFF
/
EXIT

What are you running it on?

[TheFaQQer]

SELECT SUBSTR(col1||','||col2||','||col3,1,71)
FROM foo;

Am I right in thinking this is potentially misleading? - if there are 71 characters in column 1 then you lose the data from columns 2 and 3.

SELECT SUBSTR(col1,1,23)||','||SUBSTR(col2,1,24)||','||SU BSTR(col3,1,24)
FROM foo;

(or whichever length you wanted to take from each field)

No, it's not misleading - the requirement was to output no more than 71 characters, which this does. If there are more than 71 characters in the concatenated columns, then these will not appear.

[KentPhilip]

In SQL Server 2000 the following works:

select LEFT(columnname, 71)
from tablename

SUBSTR gives a syntax error, and you need SUBSTRING instead.

[Robwg]

Just use a combo of substr (for knocking characters off) and RPAD (for adding spaces on) to fix the record at 70 characters (or whatever it is).

So

select RPAD(substr(column,1,72),72) from tablex

[MrMark]

SELECT SUBSTR(col1||','||col2||','||col3,1,71)
FROM foo;

Am I right in thinking this is potentially misleading? - if there are 71 characters in column 1 then you lose the data from columns 2 and 3.

SELECT SUBSTR(col1,1,23)||','||SUBSTR(col2,1,24)||','||SU BSTR(col3,1,24)
FROM foo;

(or whichever length you wanted to take from each field)

[TheFaQQer]

I think I've done it. Instead of this:

SELECT *
FROM table_name;

do this:

SELECT SUBSTR(column_name,1,71)
FROM table_name;

which I think works because there's only one column in the table.

That will do it if there is only one column in the table.

Otherwise:

SELECT SUBSTR(col1||','||col2||','||col3,1,71)
FROM foo;


would give you the first 71 characters of coumn1,2 and 3 separated by a comma.

[OrangeHopper]

You might be able to do it using a COLUMN ... FORMAT modifier along with SET WRAP.

SET WRAP OFF
COLUMN column_name FORMAT A71


I've not tried this so, as usual, I could be wrong.

[Moscow Mule]

Yep, that would do it.

[Moose423956]

I think I've done it. Instead of this:

SELECT *
FROM table_name;

do this:

SELECT SUBSTR(column_name,1,71)
FROM table_name;

which I think works because there's only one column in the table.

[Moscow Mule]

Can you use CAST instead?

[Moose423956]

I'm trying to use the SQL spool command to set a max record length for the output file I'm spooling to. However, when the data exceeds the max record length, it just wraps the data onto the next record.

I'm looking for a way of stopping it writing the extra record, I just want it to truncate the data at the max record length. I can't see an obvious way to do this, and being new to SQL I can't work out the best way to do it. Any advice would be appreciated. Thanks.

For example:

CLEAR BREAKS
SET HEADING OFF
SET LINESIZE 71
SET ECHO OFF
SET SERVEROUTPUT OFF
SET TERMOUT OFF
SET FEEDBACK OFF
SET PAGESIZE 0

SPOOL D:\Data\outputfile.dat
SELECT *
FROM table_name;
SPOOL OFF
/
EXIT