All SQL database systems have DDL (data definition language) and DML (data manipulation language) tools to support the creation and maintenance of databases.
DDL is responsible for tasks related to database creation, modification and maintenance while DML is responsible for tasks related to the manipulation of data, which essentially means DML is the programming interface of the database.
The DML also supports functions and these are referred to as SQL functions. SQL functions are small programs that may have zero or more input parameters but can return only one value.
You can create your own functions in SQL Server (Transact-SQL). Let's take a closer look
CREATE FUNCTION [schema_name.]function_name
( [ @parameter [ AS ] [type_schema_name.] datatype
[ = default ] [ READONLY ]
, @parameter [ AS ] [type_schema_name.] datatype
[ = default ] [ READONLY ] ]
)
RETURNS return_datatype
[ WITH { ENCRYPTION
| SCHEMABINDING
| RETURNS NULL ON NULL INPUT
| CALLED ON NULL INPUT
| EXECUTE AS Clause ]
[ AS ]
BEGIN
[declaration_section]
executable_section
RETURN return_value
END;
Let's look at an example of how to create a function in SQL Server (Transact-SQL).
CREATE FUNCTION ReturnSite
( @site_id INT )
RETURNS VARCHAR(50)
AS
BEGIN
DECLARE @site_name VARCHAR(50);
IF @site_id < 10
SET @site_name = 'TechOnTheNet.com';
ELSE
SET @site_name = 'CheckYourMath.com';
RETURN @site_name;
END;
This function is called ReturnSite. It has one parameter called @site_id which is an INT datatype. The function returns a VARCHAR(50) value, as specified by the RETURNS clause.
| Function | Description |
|---|---|
| ASCII | Returns the ASCII value for the specific character |
| CHAR | Returns the character based on the ASCII code |
| CHARINDEX | Returns the position of a substring in a string |
| CONCAT | Adds two or more strings together |
| Concat with + | Adds two or more strings together |
| CONCAT_WS | Adds two or more strings together with a separator |
| DATALENGTH | Returns the number of bytes used to represent an expression |
| DIFFERENCE | Compares two SOUNDEX values, and returns an integer value |
| FORMAT | Formats a value with the specified format |
| LEFT | Extracts a number of characters from a string (starting from left) |
| LEN | Returns the length of a string |
| LOWER | Converts a string to lower-case |
| LTRIM | Removes leading spaces from a string |
| NCHAR | Returns the Unicode character based on the number code |
| PATINDEX | Returns the position of a pattern in a string |
| QUOTENAME | Returns a Unicode string with delimiters added to make the string a valid SQL Server delimited identifier |
| REPLACE | Replaces all occurrences of a substring within a string, with a new substring |
| REPLICATE | Repeats a string a specified number of times |
| REVERSE | Reverses a string and returns the result |
| RIGHT | Extracts a number of characters from a string (starting from right) |
| RTRIM | Removes trailing spaces from a string |
| SOUNDEX | Returns a four-character code to evaluate the similarity of two strings |
| SPACE | Returns a string of the specified number of space characters |
| STR | Returns a number as string |
| STUFF | Deletes a part of a string and then inserts another part into the string, starting at a specified position |
| SUBSTRING | Extracts some characters from a string |
| TRANSLATE | Returns the string from the first argument after the characters specified in the second argument are translated into the characters specified in the third argument. |
| TRIM | Removes leading and trailing spaces (or other specified characters) from a string |
| UNICODE | Returns the Unicode value for the first character of the input expression |
| UPPER | Converts a string to upper-case |