SAP SQL Anywhere

Question

I am trying to set a variable equal to a string and then output that into a file that must be formatted exactly line by line.

I have tried this:

BEGIN 
declare @str long varchar;
set @str = '
line1
line2
line3';

UNLOAD SELECT @str to 'c:\String.txt';
END

and the result is this: '\x0aline1\x0aline2\x0aline3'

After some research I also tried this:

BEGIN
declare @str long varchar;
set @str = '\nline1 \nline2 \nline3';
UNLOAD SELECT @str to 'c:\String.txt';
END

and the result is the same: '\x0aline1 \x0aline2 \x0aline3'

After Calvin's comment I tried this:

BEGIN
declare @str long varchar;
set @str = CHAR(10) + CHAR(13)+ 'line1' + CHAR(10)+CHAR(13) + 'line2' + CHAR(10)+CHAR(13) + 'line3';
UNLOAD SELECT @str to 'c:\String.txt';
END

and the result is: '\x0a\x0dline1\x0a\x0dline2\x0a\x0dline3'

How do I make a real new line appear in a txt file?

Also, how would I remove the single quotes that surround the results in the text file?

Martin and Volker's answer both work. This is the full code for Martin's answer:

begin
declare @str long varchar;
set @str = CHAR(13)+CHAR(10) + 'line1' + CHAR(13)+CHAR(10) + 'line2' + CHAR(13)+CHAR(10) + 'line3';
unload select @str to 'c:\String.txt' QUOTES OFF ESCAPES OFF;
end

Accepted Answer

I don't know whether there is a better way by particular options of UNLOAD SELECT (I tried ESCAPES ON/OFF, QUOTES ON/OFF and the like).

EDIT: According to John's comments on Martin's answer, the following seems to be the correct platfrom-independant solution.

QUOTES OFF will remove the disturbing quotes around the output.

But the easiest thing may be to give UNLOAD SELECT a usually result set, i.e. the different lines treated as different rows. That can easily be done with the help of the sa_split_list function (here used with the select from procedure-syntax):

select * from sa_split_list('line1\nline2\nline3', '\n') order by line_num;

returns a resultset as following

line_num row_value
1        line1   
2        line2
3        line3

Therefore just selecting the row_value column will give you the needed text file:

BEGIN
declare @str long varchar;
set @str = 'line1\nline2\nline3';
UNLOAD select row_value from sa_split_list(@str, '\n') order by line_num
   to 'C:\String.txt' quotes off;
END

Accepted Answer

1

Use \x0d\x0a for the carriage return inside your string (Calvin just mixed the order) and use

UNLOAD SELECT @str to 'c:\String.txt' Quote '' escapes off;

By the way a lot of software is already recognizing the \x0a as a line feed e.g. Microsoft Wordpad...

permanent link

answered 01 Oct '10, 07:18

Martin
9.0k●130●169●257
accept rate: 14%

In my test (with SA 12.0.0.2566), this leads to just one line with a graphical symbol instead of the n, when opened in a plain Windows text editor like Notepad. (Can't show here, as SQLA seems to interpret it correctly...)

(01 Oct '10, 07:41) Volker Barth

2

QUOTES OFF can be used instead of QUOTE '' but if the data is sitting in a varchar variable needs to go to a file as-is, xp_write_file can be used. In both cases (ESCAPES OFF or xp_write_file), the string will be written as-is and that means the n characters will go out as a UNIX linefeed character (character 0A). For DOS end-of-lines you will need to put in the carriage returns yourself using something such as xp_write_file( 'line1x0dnline2x0dn' ). Most Windows programs will accept the UNIX end-of-line though apparently not Notepad as Volker notes above.

(01 Oct '10, 10:03) John Smirnios

@John: So the newline-handling is different between "n" within a plain string variable and the row delimiter uwed within resultsets? When unloading resultsets, I never had to deal with LF/CR-LF/CR platform differences... Is the server "smart enough" to use the according platform representation for the row delimiter automatically?

(01 Oct '10, 11:03) Volker Barth

1

Yes... a linefeed row delimiter is handled specially. On input we accept LF or CRLF on all platforms and on output we convert LF to CRLF (but only on DOS-ish platforms). It's all just to appease the crazy DOS world :) We can't legitimately do such conversions inside the actual data values since the intention is to be able to write values and get the exact same values back when you read them -- including when the data is written on one platform and read on another.

(01 Oct '10, 12:19) John Smirnios

@John: Thanks for the clarification! And I'm glad that you don't have to handle the additional representation for newline on Mac OS V9 and before, i.e. the mere "CR" ... cf. http://en.wikipedia.org/wiki/Newline :)

(01 Oct '10, 12:33) Volker Barth

flat view

Answer 3

Use whatever combination of '\x0D' and/or '\x0A' works with whatever client software and operating system you are using to display the text.

Personally, I work in the world of Windoze, and I find that \x0D\x0A is pretty reliable... works with both Wordpad and Notepad ( what, there are other text editors? :)

Note that life can be very different in the Unix world. Utility programs exist to convert text files when moving between environments... and religious wars exist, even within Sybase, on which newline is The One True NewLine... a Great Schism formed when folks stopped using mechanical teletypewriters, and the spittle has flown ever since.

Even Notepad differs from Wordpad in the way they handle non-\x0D\x0A line breaks... sometimes you can view text in one program, but it appears all on one line in the other, depending on what combination of one, two or three '\x0A' and '\x0D' character(s) is/are being used.

FWIW I absolutely detest all forms of logical escape sequences like '\n' since you NEVER REALLY KNOW what you are getting, I stick to the physical escape sequences like '\x0A' and '\x0D' ...but that's just me.

For more than you ever wanted to know on the subject, start here and follow the links: http://en.wikipedia.org/wiki/Control_character

SAP SQL Anywhere

How to make a new line within a string

Follow this question

Related questions