The Microsoft Open Database Connectivity (ODBC) interface is a C programming language interface that makes it possible for applications to access data from a variety of database management systems (DBMSs).
Microsoft has standardized ODBC data source from SQL Server 2012 onwards. For more information, please refer this blog post.
ODBC is a standard definition of an application programming interface (API) used to access data in relational or indexed sequential access method (ISAM) databases. SQL Server supports ODBC, via the SQL Server Native Client ODBC driver. Learn more about SQL Server Native Client (ODBC).
In this blog post, I will be talking about ODBC Scalar Functions and its use in SQL Server.
Scalar Function Escape Sequence in ODBC
ODBC uses escape sequences for executing scalar functions.
Syntax
{fn scalar-function}
ODBC scalar function category
- String Functions
- Numeric Functions
- Time, Date, and Interval Functions
- System Functions
- Explicit Data Type Conversion Function
- SQL-92 CAST Function
Download ODBC Scalar Functions cheat sheet. This document is created for my personal reference. But, you can download this document without any restrictions.
So, we have learned about ODBC, scalar function escape sequence in ODBC and scalar functions. Now, I am going to talk about how to use ODBC scalar functions in the SQL statements.
ODBC Scalar Functions in T-SQL
We can use ODBC Scalar Functions in Transact-SQL statements. These statements are interpreted by SQL Server automatically. These functions can be used in stored procedures and user-defined functions.
T-SQL Syntax
{fn <function_name> [ (<argument>,….n) ] }
In simple words, we must use ODBC scalar function escape sequence and we can include any ODBC scalar functions inside the escape sequence.
Example
1. Print the current date and time using ODBC scalar function
select {fn now()} as DataTime
2. String concatenation function
Declare @str1 as varchar(100), @str2 as varchar(100)
set @str1 = ‘SQL SERVER’
set @str2 = ‘ RIDER’
select {fn CONCAT(@str1, @str2)} as BlogName
These simple examples are given here to help you to understand the use of ODBC scalar function in T-SQL. I hope you have understood the concept of the ODBC and its functions usage in SQL Server.
SQL Server Rider 