TSQL dropping newline after \

Question

Post reply

TSQL dropping newline after \

alexrains

SSC Veteran

Points: 266
More actions
September 13, 2004 at 11:55 am

#89356

We are using a text field in out database to store and version control simple scripts. Problem is, when inserting text data sometimes newline characters get dropped when they appear directly after \
Try this code:

drop table test
go
create table test (script varchar(1000) null)
go
insert into test (script) values ('
file:\test\path\stuff\here\
next line.
')
insert into test (script) values ('
file:\test\path\stuff\here\\
next line.
')
insert into test (script) values ('
file:\test\path\stuff\here\\
next line.
')
go
select script + '#' from test

Note that the \ characters within the path are saved correctly. It is only when a \ is followed directly by a newline character that the both characters disapear.
anyone ever seen this before? My workaround will be to trap this in application logic; my concern is that there are other problematic character combinations that I dont know about?

--Alex

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

chrisleonard SSCertifiable Points: 5030 More actions · Answer 1

Warning: this post contains potentially offensive language. That's right, I'm going to use the "S" word.

Sybase.

Sybase had behavior in its version of Transact-SQL that allowed you to continue a string on the next line by including a backslash at the end of a line, as a continuation character. This seems hauntingly similar, but I had no idea that you could do this in SQL Server. Granted, the behavior is a little different, but it makes you wonder, doesn't it?

I don't know if this behavior is by design, or if it is a bug (didn't MS say they threw out all that Sybase code? ). At any rate, I thought this tidbit of knowledge might be interesting to you

And by the way, SQL Server 2005 Beta 2 handles this the same way as SQL Server 2000.

Unless somebody here has more information on this, it would be great if somebody could get visibility for this issue with somebody on the SQL Team. It seems like this is a big potential problem, and it's the kind of "sleeper" problem that could go unnoticed for a while.

Cheers,

Chris

alexrains SSC Veteran Points: 266 More actions · Answer 2

Thanks Chris, at least I know that I'm not just going crazy... :^)

I'm amazed that this has never manifested as a bug before now for anyone else..

ra

Alex