SQL Question - setting a fixed record length using Spool

[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

 

[Moscow Mule]

Can you use CAST instead?

 

[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]

Yep, that would do it.

 

[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.

 

[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.

 

[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)||','||SUBSTR(col3,1,24)
FROM foo;

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

 

[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

 

[KentPhilip]

In SQL Server 2000 the following works:

select LEFT(columnname, 71)
from tablename

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

 

[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)||','||SUBSTR(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.