SQL Scalar functions
In this post we will learn basic functions in sql and explain how to use functions with an example.
SQL has many in-built functions for performing processing on data.
There are basic two type of functions in sql.
In this post i will explain about SQL scalar function.
SQL scalar functions – SQL scalar functions return a single value, base on the input value.
Here is the list of useful scalar functions:
UCASE() – The UCASE() function used to convert text to uppercase and return it.
The syntax of UCASE() for SQL:
SELECT UCASE(col_name) FROM Table_Name
The syntax for SQL Server:
SELECT UPPER(col_name) FROM Table_Name
LCASE() – The LCASE() function used to convert text to lowercase and return it.
The syntax of LCASE() is:
SELECT LCASE(col_name) FROM Table_Name
The syntax for SQL Server
SELECT LOWER(col_name) FROM Table_Name
MID() – The MID() function is used to return exact text from given text field.
The syntax of MID() for SQL :
SELECT MID(col_name, strat, length) as some col_name FROM Table_Name
start – starting position of text
length – length or number of character to return.
In SQL Server we use SUBSTRING() function.
The syntax of SUBSTRING() in SQL Server :
SELECT SUBSTRING(col_name, strat, length) as some col_name FROM Table_Name
LEN() – The LEN() function used to returns the length of text field.
The syntax of LEN() is :
SELECT LEN(col_name) FROM Table_Name
ROUND() – The ROUND() function used to rounds the value and returns it.
The syntax of ROUND() is:
SELECT ROUND(col_name, decimal) FROM Table_Name
NOW() – The function NOW() is returns current system date and time.
The syntax of NOW() is :
SELECT NOW() FROM Table_Name
In SQL Server we use GETDATE() function to get current date and time.
The syntax of GETDATE() is :
SELECT GETDATE();
SELECT GETDATE() FROM Table_Name
FORMAT() – The FORMATE() function is specify format how a data is to be displayed.
The syntax of FORMATE() is :
SELECT FORMAT(col_name, format) FROM Table_Name