Constants (T-SQL)

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

Character string constants

Character string constants are enclosed in single quotation marks and include alphanumeric characters (a-z, A-Z, and 0-9) and special characters, such as !, @, and #. Character strings are evaluated through the code page of the computer.

If the QUOTED_IDENTIFIER option has been set OFF for a connection, character strings can also be enclosed in double quotation marks, but the Microsoft® OLE DB Provider for SQL Server and ODBC driver automatically use SET QUOTED_IDENTIFIER ON. The use of single quotation marks is recommended.

If a character string enclosed in single quotation marks contains an embedded quotation mark, represent the embedded single quotation mark with two single quotation marks. This is not necessary in strings embedded in double quotation marks.

Examples of character strings are:

'Cincinnati'

'O''Brien'

'Process X is 50% complete.'

'The level for job_id: %d should be between %d and %d.'

"O'Brien"

  

Empty strings are represented as '' (two single quotation marks with nothing in between). In 6.x compatibility mode, an empty string is treated as a single space.

Unicode strings

Unicode strings have a format similar to character strings, but are preceded by an N identifier (N stands for National Language in the SQL-92 standard). The N prefix must be uppercase. For example, ‘Michél’ is a character constant while N’Michél’ is a Unicode constant. Unicode constants are interpreted as Unicode data, and are not evaluated using a code page. Unicode data is stored using 2 bytes per character, as opposed to 1 byte per character for character data. For more information, see Using Unicode Data.

Binary constants

Binary constants have the suffix 0x and are a string of hexadecimal numbers. They are not enclosed in quotation marks. Examples of binary strings are:

0xAE

0x12Ef

0x69048AEFDD010E

0x  (empty binary string)

Bit constants

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

Datetime constants

Datetime constants are represented using character date values in specific formats, enclosed in single quotation marks. For more information about the formats for datetime constants, see Using Date and Time Data. Examples of date constants are:

'April 15, 1998'

'15 April, 1998'

'980415'

'04/15/98'

  

Examples of time constants are:

'14:30:24'

'04:24 PM'

Integer constants

Integer constants are represented by a string of numbers that are not enclosed in quotation marks and do not contain decimal points. Integer constants must be whole numbers; they cannot contain decimals. Examples of integer constants are:

1894

2

  

Decimal constants

Decimal constants are represented by a string of numbers that are not enclosed in quotation marks and contain a decimal point. Examples of decimal constants are:

1894.1204

2.0

  

Float and real constants

Float and real constants are represented using scientific notation. Examples of float or real values are:

101.5E5

0.5E-2

  

Money constants

Money constants are represented as string of numbers with an optional decimal point and an optional currency symbol as a prefix. They are not enclosed in quotation marks. Examples of money constants are:

$12

$542023.14

  

Uniqueidentifier constants

Uniqueidentifier constants are a string representing a globally unique identifier (GUID) value. They can be specified in either a character or binary string format. Both of these examples specify the same GUID:

'6F9619FF-8B86-D011-B42D-00C04FC964FF'

0xff19966f868b11d0b42d00c04fc964ff

  

Specifying Negative and Positive Numbers

To indicate whether a number is positive or negative, apply the + or - unary operators to a numeric constant. This creates a numeric expression that represents the signed numeric value. Numeric constants default to positive if the + or - unary operators are not applied.

• Signed integer expressions:

  +145345234

  -2147483648

    

• Signed decimal expressions:

  +145345234.2234

  -2147483648.10

    

• Signed float expressions:

  +123E-3

  -12E5

    

• Signed money expressions:

  -$45.56

  +$423456.99

    

See Also