http://www.sqlservercentral.com/blogs/philfactor/2010/07/29/the-killer-backslach-in-tsql/

Printed 2014/09/16 05:46PM

The Killer Backslash in TSQL

By Phil Factor, 2010/07/29

--it is quite startling to see that this, in TSQL, executes.
Select    \
/*
---------------------
0.00
 
(1 row(s) affected)
 
so does this....
*/
Select    null  /\/\/\/\/\/\/\/\/\/\/\/\
/*
---------------------
NULL
 
(1 row(s) affected)
*/
/* Good old SQL Server. We can execute smileys*/
Select    \-0   [(-:]
/* however, there is a serious side to this. Now go to Adventureworks (here I'm in good ol' SQL Server 2005) and execute this.*/
 
SELECT
      [TerritoryID],/*Primary key for SalesTerritory records.*/
      [Name],/*Sales territory description*/
      [CountryRegionCode],/*ISO standard country or region code. Foreign key to CountryRegion.CountryRegionCode. */
      [Group],/*Geographic area to which the sales territory belong.*/
      [SalesYTD],/*Sales in the territory year to date.*/
\     [SalesLastYear],/*Sales in the territory the previous year.*/
      [CostYTD],/*Business costs in the territory year to date.*/
      [CostLastYear],/*Business costs in the territory the previous year.*/
      [rowguid],/*ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.*/
      [ModifiedDate] /*Date and time the record was last updated.*/
FROM  [Sales].[SalesTerritory]  /*Sales territory lookup table.*/
 
/* notice anything wrong? Yeah. All your Sales last year have been zeroed. a pesky backslash has crept in. You just put that backslash in some important code that is calculating your sales commission or your sales performance. What happens? Maybe some salesman loases commission. Maybe someone loses his job as a result. Whose fault is it? You'd better get checking! */
 
/* now take out the backslash and observe the difference. Yes, far better sales figures */
 
/* so what is going on here?
Well. It turns out that the parser is treating the \ as a currency symbol. No, you won't find a currency called the back-slash, but this is the wacky world of Microsoft. To add to the confusion, with money input, if you leave out the value, it assumes that you want to enter 0 back-slashes. It also allows as many spaces as you like between the currency symbol and the value, even if it exists.*/
 
Select  \-0-\  1-0-1    --Star wars fighters
 
 
/*
---------------------
-2.00
 
(1 row(s) affected)*/
 
Select\   ThisisVerySilly
 

/*
ThisisVerySilly
---------------------
0.00
 
(1 row(s) affected)*/
 
/*

This is such an insidious bug that I feel it is important to warn you about it. I believe it has always been in SQL Server but I reckon it is worth running the occasional check on your code to make sure that a backslash hasn't crept in when you're sleepily typing in code late in the day. */;


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.