# ODBC 純量函數 (Transact-SQL)ODBC Scalar Functions (Transact-SQL)

## 使用方式Usage

`SELECT {fn <function_name> [ (<argument>,....n) ] }`

## 函數Functions

### 字串函數String Functions

BIT_LENGTH( string_exp ) (ODBC 3.0)BIT_LENGTH( string_exp ) (ODBC 3.0) 傳回字串運算式的長度 (以位元為單位)。Returns the length in bits of the string expression.

CONCAT( string_exp1,string_exp2) (ODBC 1.0)CONCAT( string_exp1,string_exp2) (ODBC 1.0) 傳回字元字串，表示將 string_exp2 串連至 string_exp1 的結果。Returns a character string that is the result of concatenating string_exp2 to string_exp1. 產生的字串為 DBMS 相依。The resulting string is DBMS-dependent. 例如，如果由 string_exp1 所代表的資料行包含 NULL 值，DB2 就會傳回 NULL，但是 SQL ServerSQL Server 會傳回非 NULL 字串。For example, if the column represented by string_exp1 contained a NULL value, DB2 would return NULL but SQL ServerSQL Server would return the non-NULL string.
OCTET_LENGTH( string_exp ) (ODBC 3.0)OCTET_LENGTH( string_exp ) (ODBC 3.0) 傳回字串運算式的長度 (以位元組為單位)。Returns the length in bytes of the string expression. 結果就是不小於位元數的最小整數除以 8。The result is the smallest integer not less than the number of bits divided by 8.

### 數值函數Numeric Function

TRUNCATE( numeric_exp, integer_exp) (ODBC 2.0)TRUNCATE( numeric_exp, integer_exp) (ODBC 2.0) 傳回 numeric_exp (截斷至小數點右邊的 integer_exp 個位置)。Returns numeric_exp truncated to integer_exp positions right of the decimal point. 若 integer_exp 為負數，則 numeric_exp 會截斷至小數點左邊的 |integer_exp| 個位置。If integer_exp is negative, numeric_exp is truncated to |integer_exp| positions to the left of the decimal point.

### 時間、日期和間隔函數Time, Date, and Interval Functions

CURRENT_DATE( ) (ODBC 3.0)CURRENT_DATE( ) (ODBC 3.0) 傳回目前的日期。Returns the current date.
CURDATE( ) (ODBC 3.0)CURDATE( ) (ODBC 3.0) 傳回目前的日期。Returns the current date.
CURRENT_TIME`[( time-precision )]` (ODBC 3.0)CURRENT_TIME`[( time-precision )]` (ODBC 3.0) 傳回目前的當地時間。Returns the current local time. 其中 time-precision 引數會決定傳回值的秒數有效位數。The time-precision argument determines the seconds precision of the returned value
CURTIME() (ODBC 3.0)CURTIME() (ODBC 3.0) 傳回目前的當地時間。Returns the current local time.
DAYNAME( date_exp ) (ODBC 2.0)DAYNAME( date_exp ) (ODBC 2.0) 傳回字元字串，其中針對 date_exp 的日期部分包含資料來源專用的日期名稱。Returns a character string that contains the data-source-specific name of the day for the day part of date_exp. 例如，若為使用英文的資料來源，則名稱為 Sunday 到 Saturday 或 Sun.For example, the name is Sunday through Saturday or Sun. 到 Sat.；through Sat. for a data source that uses English. 若為使用德文的資料來源，則名稱為 Sonntag 到 Samstag。The name is Sonntag through Samstag for a data source that uses German.
DAYOFMONTH( date_exp ) (ODBC 1.0)DAYOFMONTH( date_exp ) (ODBC 1.0) 根據 date_exp 中的月份欄位，以整數值傳回月份的日期。Returns the day of the month, based on the month field in date_exp, as an integer. 傳回值的範圍介於 1-31 之間。The return value is in the range of 1-31.
DAYOFWEEK( date_exp ) (ODBC 1.0)DAYOFWEEK( date_exp ) (ODBC 1.0) 根據 date_exp 中的週欄位，以整數值傳回當週的日期。Returns the day of the week based on the week field in date_exp as an integer. 傳回值的範圍介於 1-7 之間，其中 1 代表星期日。The return value is in the range of 1-7, where 1 represents Sunday.
HOUR( time_exp ) (ODBC 1.0)HOUR( time_exp ) (ODBC 1.0) 根據 time_exp 中的小時欄位，以整數值 (範圍介於 0-23 之間) 傳回小時。Returns the hour, based on the hour field in time_exp, as an integer value in the range of 0-23.
MINUTE( time_exp ) (ODBC 1.0)MINUTE( time_exp ) (ODBC 1.0) 根據 time_exp 中的分鐘欄位，以整數值 (範圍介於 0-59 之間) 傳回分鐘。Returns the minute, based on the minute field in time_exp, as an integer value in the range of 0-59.
SECOND( time_exp ) (ODBC 1.0)SECOND( time_exp ) (ODBC 1.0) 根據 time_exp 中的秒數欄位，以整數值 (範圍介於 0-59 之間) 傳回秒數。Returns the second, based on the second field in time_exp, as an integer value in the range of 0-59.
MONTHNAME( date_exp ) (ODBC 2.0)MONTHNAME( date_exp ) (ODBC 2.0) 傳回字元字串，其中針對 date_exp 的月份部分包含資料來源專用的月份名稱。Returns a character string that contains the data-source-specific name of the month for the month part of date_exp. 例如，若為使用英文的資料來源，則名稱為 January 到 December 或 Jan. 到 Dec.。For example, the name is January through December or Jan. through Dec. for a data source that uses English. 若為使用德文的資料來源，則名稱為 Januar 到 Dezember。The name is Januar through Dezember for a data source that uses German.
QUARTER( date_exp ) (ODBC 1.0)QUARTER( date_exp ) (ODBC 1.0) 傳回 date_exp 中的季度成為整數值 (範圍介於 1-4 之間，其中 1 代表 1 月 1 日到 3 月 31 日)。Returns the quarter in date_exp as an integer value in the range of 1-4, where 1 represents January 1 through March 31.
WEEK( date_exp ) (ODBC 1.0)WEEK( date_exp ) (ODBC 1.0) 根據 date_exp 中的週欄位，以整數值 (範圍介於 1-53 之間) 傳回當年的週數。Returns the week of the year, based on the week field in date_exp, as an integer value in the range of 1-53.

## 範例Examples

### A.A.在預存程序中使用 ODBC 函數Using an ODBC function in a stored procedure

``````CREATE PROCEDURE dbo.ODBCprocedure
(
@string_exp nvarchar(4000)
)
AS
SELECT {fn OCTET_LENGTH( @string_exp )};
``````

### B.B.在使用者定義函數中使用 ODBC 函數Using an ODBC Function in a user-defined function

``````CREATE FUNCTION dbo.ODBCudf
(
@string_exp nvarchar(4000)
)
RETURNS int
AS
BEGIN
DECLARE @len int
SET @len = (SELECT {fn OCTET_LENGTH( @string_exp )})
RETURN(@len)
END ;

SELECT dbo.ODBCudf('Returns the length.');
--Returns 38

``````

### C.C.在 SELECT 陳述式中使用 ODBC 函數Using an ODBC functions in SELECT statements

``````DECLARE @string_exp nvarchar(4000) = 'Returns the length.';
SELECT {fn BIT_LENGTH( @string_exp )};
-- Returns 304
SELECT {fn OCTET_LENGTH( @string_exp )};
-- Returns 38

SELECT {fn CONCAT( 'CONCAT ','returns a character string')};
-- Returns CONCAT returns a character string
SELECT {fn TRUNCATE( 100.123456, 4)};
-- Returns 100.123400
SELECT {fn CURRENT_DATE( )};
-- Returns 2007-04-20
SELECT {fn CURRENT_TIME(6)};
-- Returns 10:27:11.973000

DECLARE @date_exp nvarchar(30) = '2007-04-21 01:01:01.1234567';
SELECT {fn DAYNAME( @date_exp )};
-- Returns Saturday
SELECT {fn DAYOFMONTH( @date_exp )};
-- Returns 21
SELECT {fn DAYOFWEEK( @date_exp )};
-- Returns 7
SELECT {fn HOUR( @date_exp)};
-- Returns 1
SELECT {fn MINUTE( @date_exp )};
-- Returns 1
SELECT {fn SECOND( @date_exp )};
-- Returns 1
SELECT {fn MONTHNAME( @date_exp )};
-- Returns April
SELECT {fn QUARTER( @date_exp )};
-- Returns 2
SELECT {fn WEEK( @date_exp )};
-- Returns 16
``````

## 範例：Azure SQL 資料倉儲Azure SQL Data Warehouse 和 平行處理資料倉儲Parallel Data WarehouseExamples: Azure SQL 資料倉儲Azure SQL Data Warehouse and 平行處理資料倉儲Parallel Data Warehouse

### D.D.在預存程序中使用 ODBC 函數Using an ODBC function in a stored procedure

``````CREATE PROCEDURE dbo.ODBCprocedure
(
@string_exp nvarchar(4000)
)
AS
SELECT {fn BIT_LENGTH( @string_exp )};
``````

### E.E.在使用者定義函數中使用 ODBC 函數Using an ODBC Function in a user-defined function

``````CREATE FUNCTION dbo.ODBCudf
(
@string_exp nvarchar(4000)
)
RETURNS int
AS
BEGIN
DECLARE @len int
SET @len = (SELECT {fn BIT_LENGTH( @string_exp )})
RETURN(@len)
END ;

SELECT dbo.ODBCudf('Returns the length in bits.');
--Returns 432

``````

### F.F.在 SELECT 陳述式中使用 ODBC 函數Using an ODBC functions in SELECT statements

``````DECLARE @string_exp nvarchar(4000) = 'Returns the length.';
SELECT {fn BIT_LENGTH( @string_exp )};
-- Returns 304

SELECT {fn CONCAT( 'CONCAT ','returns a character string')};
-- Returns CONCAT returns a character string
SELECT {fn CURRENT_DATE( )};
-- Returns todays date
SELECT {fn CURRENT_TIME(6)};
-- Returns the time

DECLARE @date_exp nvarchar(30) = '2007-04-21 01:01:01.1234567';
SELECT {fn DAYNAME( @date_exp )};
-- Returns Saturday
SELECT {fn DAYOFMONTH( @date_exp )};
-- Returns 21
SELECT {fn DAYOFWEEK( @date_exp )};
-- Returns 7
SELECT {fn HOUR( @date_exp)};
-- Returns 1
SELECT {fn MINUTE( @date_exp )};
-- Returns 1
SELECT {fn SECOND( @date_exp )};
-- Returns 1
SELECT {fn MONTHNAME( @date_exp )};
-- Returns April
SELECT {fn QUARTER( @date_exp )};
-- Returns 2
SELECT {fn WEEK( @date_exp )};
-- Returns 16
``````