Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

What is the usage of '\' and '$' in T-SQL?

As I found (in SQL Server books):

\ (Backslash) (Transact-SQL)
Breaks a long string constant into two or more lines for readability.

and

SELECT Clause (Transact-SQL) ... $IDENTITY | $ROWGUID
And
$PARTITION (Transact-SQL)
Returns the partition number into which a set of partitioning column values would be mapped for any specified partition function.

of usage of \ and $ in T-SQL specially SQL Server.

Now, I have a query like this:

SELECT \ a, $ b, \11 c, $12 d;

That have a valid result like this:

a    | b    | c     | d 
-----+------+-------+-------
0.00 | 0.00 | 11.00 | 12.00

I think there is something that I can't find it about this characters.

Edit :
I found that if a number comes after currency symbols, SQL Server will remove the defined symbol and store the value as a money data type:

And I think, SQL Server translate a single currency symbol that is the last phrase in a part -these parts are between + and -- of formula to 0.00 and only at the end of the part like -$, ($), (12 + $), ($) + 12, $ * (12 - $) or so on, and not $ + 1, 2 * $ - 1. Also I found $ 2 is same as $2.

All above behavior is same for \ that means SQL Server thinks \ is a currency symbol!!!

like image 402
shA.t Avatar asked May 17 '15 13:05

shA.t


People also ask

What is the use of %s in SQL?

%s is a placeholder used in functions like sprintf. Check the manual for other possible placeholders. $sql = sprintf($sql, "Test"); This would replace %s with the string "Test".

What is use of coalesce in SQL?

The SQL server's Coalesce function is used to handle the Null values. The null values are replaced with user-defined values during the expression evaluation process. This function evaluates arguments in a particular order from the provided arguments list and always returns the first non-null value.

How do I use multiple wildcards in SQL?

There are two wildcards used in conjunction with the LIKE operator. The percent sign represents zero, one or multiple characters. The underscore represents a single number or character. These symbols can be used in combinations.

What is the meaning of the prefix N in T-SQL statements?

You may have seen Transact-SQL code that passes strings around using an N prefix. This denotes that the subsequent string is in Unicode (the N actually stands for National language character set). Which means that you are passing an NCHAR, NVARCHAR or NTEXT value, as opposed to CHAR, VARCHAR or TEXT.


1 Answers

I thought to check the datatype and each as you'll notice each returns the datatype money.

WITH CTE
AS
(
    SELECT \ a, $ b, \11 c, $12 d   
)

SELECT  SQL_VARIANT_PROPERTY(a,'baseType') a,
        SQL_VARIANT_PROPERTY(b,'baseType') b,
        SQL_VARIANT_PROPERTY(c,'baseType') c,
        SQL_VARIANT_PROPERTY(d,'baseType') d
FROM CTE

Results:

a                              b                              c                              d
------------------------------ ------------------------------ ------------------------------ ------------------------------
money                          money                          money                          money
like image 150
Stephan Avatar answered Oct 04 '22 08:10

Stephan