常數 (Transact-SQL)Constants (Transact-SQL)

適用於: 是SQL Server (從 2008 開始) 是Azure SQL Database 是Azure SQL 資料倉儲 是平行處理資料倉儲 APPLIES TO: yesSQL Server (starting with 2008) yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

常數也稱為常值或純量值,是一個代表特定資料值的符號。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

字元字串常數含括在單引號中,其中包括英數字元 (a-z、A-Z 和 0-9) 以及特殊字元,如驚歎號 (!)、@ 記號 (@) 和數字符號 (#)。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 exclamation point (!), at sign (@), and number sign (#). 除非利用 COLLATE 子句來指定定序,否則,字元字串常值會被指派目前資料庫的預設定序。Character string constants are assigned the default collation of the current database, unless the COLLATE clause is used to specify a collation. 使用者所輸入的字元字串是透過電腦的字碼頁來評估的,必要的話,它們會轉換成資料庫預設字碼頁。Character strings typed by users are evaluated through the code page of the computer and are translated to the database default code page if it is required.

如果連接的 QUOTED_IDENTIFIER 選項已設為 OFF,就可以將字元字串括在雙引號內,但 Microsoft SQL ServerSQL Server Native Client 提供者和 ODBC 驅動程式會自動使用 SET QUOTED_IDENTIFIER ON。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 SQL ServerSQL Server Native Client Provider and ODBC driver automatically use SET QUOTED_IDENTIFIER ON. 我們建議您使用單引號。We recommend using single quotation marks.

如果括在單引號中的字元字串包含內嵌雙引號,請用兩個單引號來表示內嵌的單引號。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 required in strings embedded in double quotation marks.

以下是字元字串的範例:The following are examples of character strings:

'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. 在 6.x 相容性模式中用單一空格來表示空字串。In 6.x compatibility mode, an empty string is treated as a single space.

字元字串常數支援增強型定序。Character string constants support enhanced collations.

注意

大於 8000 位元組的字元常數類型為 varchar(max) 資料。Character constants greater than 8000 bytes are typed as varchar(max) data.

Unicode 字串Unicode strings

Unicode 字串的格式類似於字元字串,但前面附加了 N 識別碼 (N 代表 SQL-92 標準中的國家 (地區) 語言)。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). N 前置詞必須是大寫。The N prefix must be uppercase. 例如,'Michél' 是一個字元常數,而 N'Michél' 則為 Unicode 常數。For example, 'Michél' is a character constant while N'Michél' is a Unicode constant. Unicode 常數會解譯成 Unicode 資料,並不會用字碼頁來評估。Unicode constants are interpreted as Unicode data, and are not evaluated by using a code page. Unicode 常數有定序。Unicode constants do have a collation. 這個定序主要用來控制比較和區分大小寫。This collation primarily controls comparisons and case sensitivity. 除非利用 COLLATE 子句來指定定序,否則,Unicode 常數會被指派目前資料庫的預設定序。Unicode constants are assigned the default collation of the current database, unless the COLLATE clause is used to specify a collation. Unicode 資料是利用每個字元 2 位元組來儲存的,而不是字元資料的每個字元 1 個位元組。Unicode data is stored by using 2 bytes per character instead of 1 byte per character for character data. 如需詳細資訊,請參閱 Collation and Unicode SupportFor more information, see Collation and Unicode Support.

Unicode 字串常數支援增強型定序。Unicode string constants support enhanced collations.

注意

大於 8000 位元組的 Unicode 常數類型為 nvarchar(max) 資料。Unicode constants greater than 8000 bytes are typed as nvarchar(max) data.

二進位常數Binary constants

二進位常數的前置詞是 0x,它是一個十六進位數字字串。Binary constants have the prefix 0x and are a string of hexadecimal numbers. 它們不需加上引號。They are not enclosed in quotation marks.

以下是二進位字串的範例:The following are examples of binary strings are:

0xAE  
0x12Ef  
0x69048AEFDD010E  
0x  (empty binary string)  

注意

大於 8000 位元組的二進位常數類型為 varbinary(max) 資料。Binary constants greater than 8000 bytes are typed as varbinary(max) data.

bit 常數bit constants

bit 常數會用數字 0 或 1 來表示,不需以引號括住。bit constants are represented by the numbers 0 or 1, and are not enclosed in quotation marks. 如果使用大於 1 的數字,它會轉換成 1。If a number larger than one is used, it is converted to one.

datetime 常數datetime constants

datetime 常數是利用特定格式的字元日期值來表示,並以單引號括住。datetime constants are represented by using character date values in specific formats, enclosed in single quotation marks.

以下是 datetime 常數的範例:The following are examples of datetime constants:

'December 5, 1985'  
'5 December, 1985'  
'851205'  
'12/5/98'  

datetime 常數的範例如下:Examples of datetime constants are:

'14:30:24'  
'04:24 PM'  

整數常數integer constants

integer 常數是以沒有使用引號括住的數字字串來表示,且不含小數點。integer constants are represented by a string of numbers that are not enclosed in quotation marks and do not contain decimal points. integer 常數必須是整數;不能包含小數點。integer constants must be whole numbers; they cannot contain decimals.

以下是 integer 常數的範例:The following are examples of integer constants:

1894  
2  

decimal 常數decimal constants

decimal 常數以沒有用引號括住的數字字串來表示,其中包含小數點。decimal constants are represented by a string of numbers that are not enclosed in quotation marks and contain a decimal point.

以下是 decimal 常數的範例:The following are examples of decimal constants:

1894.1204  
2.0  

float 和 real 常數float and real constants

floatreal 常數是使用科學記號表示法來表示。float and real constants are represented by using scientific notation.

以下是 floatreal 值的範例:The following are examples of float or real values:

101.5E5  
0.5E-2  

money 常數money constants

money 常數是用數字字串來表示,可選擇含有小數點,前面也可選擇附加貨幣符號。money constants are represented as string of numbers with an optional decimal point and an optional currency symbol as a prefix. money 常數未以引號括住。money constantsare not enclosed in quotation marks.

SQL ServerSQL Server 不會強制執行任何一種分組規則,如在代表貨幣的字串中,每三位數插入一個逗號 (,)。does not enforce any kind of grouping rules such as inserting a comma (,) every three digits in strings that represent money.

注意

在指定 money 常值的任何位置中,逗號都會被忽略。Commas are ignored anywhere in the specified money literal.

以下是 money 常數的範例:The following are examples of money constants:

$12  
$542023.14  

uniqueidentifier 常數uniqueidentifier constants

uniqueidentifier 常數是代表 GUID 的字串。uniqueidentifier constants are a string representing a GUID. 您可以依照字元或二進位字串格式來指定它們。They can be specified in either a character or binary string format.

下列範例指定相同的 GUID:The following examples both 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 use positive when the + or - unary operators are not applied.

帶正負號的 integer 運算式:Signed integer expressions:

+145345234
-2147483648

帶正負號的 decimal 運算式:Signed decimal expressions:

+145345234.2234
-2147483648.10

帶正負號的 float 運算式:Signed float expressions:

+123E-3
-12E5

帶正負號的 money 運算式:Signed money expressions:

-$45.56
+$423456.99

增強型定序Enhanced Collations

SQL Server 支援字元及支援增強型定序的 Unicode 字串常數。SQL Server supports character and Unicode string constants that support enhanced collations. 如需詳細資訊,請參閱 COLLATE (Transact-SQL) 子句。For more information, see the COLLATE (Transact-SQL) clause.

另請參閱See also

資料類型 (Transact-SQL)Data Types (Transact-SQL)
運算式 (Transact-SQL)Expressions (Transact-SQL)
運算子 (Transact-SQL)Operators (Transact-SQL)