Sqlserver: Constant

A constant, also known as a text value or scalar value, is a symbol that represents a specific data value. The constant format depends on the Data Type of the value it represents.

String constant

String constants are enclosed in single quotes and contain letters and numbers (a-Z, A-Z, and 0-9) and special characters, such as exclamation points (!) , At sign (@) and font size
(#). The default sorting rules of the current database will be assigned to string constants unless collate is used
Clause specifies a sorting rule for it. User-typed stringsCodePage computing is converted to the default code page of the database if necessary.

If the quoted_identifier option is set to off for a connection, the string can also be enclosed in double quotation marks, but Microsoft SQL
The local client providesProgramAnd ODBC drivers will automatically use set quoted_identifier on. We recommend that you use single quotes.

If the string in a single quotation mark contains an embedded quotation mark, you can use two single quotation marks to indicate the embedded single quotation mark. This is not necessary for strings embedded in double quotes.

The following is a string example:

	copy the Code
'cinnati' 'O' brien' 'process X is 50% complete. ' ' the level for job_id: % d shocould be between % d and % d. ' "O 'Brien"

An empty string is enclosed by two single quotes without any characters in the middle. In 6.x compatibility mode, a null string is considered as a space.

string constants support enhanced sorting rules.

Unicode string

A Unicode string is in a format similar to a common string, but it is preceded by an n identifier (N stands for the regional language in the SQL-92 standard ). N
the prefix must be an uppercase letter. For example, 'michél' is a String constant, while n'michél' is a unicode constant. Unicode constants are interpreted as Unicode
data and are not calculated using the code page. Unicode constants have sorting rules. This sorting rule is mainly used to control comparison and Case sensitivity. Assign Unicode
constants to the default sorting rules of the current database, unless the sorting rules are specified for them using the Collate clause. For character data, 2
bytes are used for each character when Unicode data is stored, instead of 1 character for each character. For more information, see .

Unicode string constants support enhanced sorting rules.

Binary constant

A binary constant has a prefix0xIt is a hexadecimal numeric string. These constants are not enclosed in quotation marks.

The following is an example of a binary string:

	Copy code
0xae 0x12ef 0x69048aefdd010e 0x (empty binary string)

Bit constant

Bit constants are represented by numbers 0 or 1 and are not enclosed in quotation marks. If you use a number greater than 1, the number is converted to 1.

Datetime constant

Datetime constants are represented by character date values in a specific format and enclosed in single quotes. For more information about the datetime constant format, see "using Date and Time data ".

The following is an example of a datetime constant:

	Copy code
'10000l 15,199 8' '15 then l, 1998' '123' '2017/98'

The following is an example of a time constant:

	copy the Code
'14: 30: 24' '04: 24 PM '

integer constant

an integer constant is represented by a numeric string that is not enclosed in quotation marks and does not contain a decimal point. All Integer constants must be numbers. They cannot contain decimals.

The following is an example of an integer constant:

	copy the Code
1894 2

decimal constant

A decimal constant is represented by a numeric string that is not enclosed by quotation marks and contains the decimal point.

The following is an example of a decimal constant:

	copy the Code
1894.1204 2.0

float and real constants

float and real constants are represented by scientific notation.

The following is an example of the float or real value:

	copy the Code
101.5e5 0.5e-2

money constant

the money constant is represented by a numeric string prefixed with an optional decimal point and an optional currency symbol. The money constant is not enclosed in quotation marks.

SQL Server 2005 does not force grouping rules of any type. For example, you can insert a comma (,) at every three digits in the string representing the currency (,).

Note:
Any comma is ignored in the specified money text.

The following is an example of a money constant:

	copy the Code
$12 $542023.14

Uniqueidentifier constant

The uniqueidentifier constant is a string that represents the guid. It can be specified in character or binary string format.

In the following example, the same GUID is specified:

	Copy code
'6f9619ff-8b86-d011-b42d-00c04fc964ff' 0xff19966f868b11d0b42d00c04fc964ff

Negative number and positive number

To indicate whether a number is a positive number or a negative number, apply the + or-unary operator to a numerical constant. This creates an expression that represents the value of a signed number. If no application + or-
Unary operator. A numeric constant uses a positive number.

• Signed integer expression:

  	Copy code
  + 145345234 -2147483648

• signed decimal expression:

  	copy the Code
  + 145345234.2234 -2147483648.10

• signed float expression:

  	copy the Code
  + 123e-3 -12e5

• Signed money expression:

  	Copy code
  -$45.56 + $423456.99

Enhanced sorting rules

The characters and Unicode string constants supported by SQL Server also support enhanced sorting rules. For more information, see collate (TRANSACT-SQL)
Clause.