SQL Scalar functions

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

Check SQL UPPER() Example

 

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

Check SQL LCASE() Example

 

The syntax for SQL Server

SELECT LOWER(col_name) FROM Table_Name

Check SQL LOWER() Example

 

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.

Check SQL MID() Example

 

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

Check SUBSTRING() Example

 

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

Leave a Reply

Your email address will not be published. Required fields are marked *

4 + 9 =


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">