SQL Functions With Example

aimtocode

SQL Functions :

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.

How to create a SQL Function:

You can create your own functions in SQL Server (Transact-SQL). Let's take a closer look

Syntax:

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;

Example

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.

SQL Server String Functions:

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
aimtocode