SQL Server day-to-day mostly used functions - String, Aggregate, Window, Date Functions in SQL
CAST() – This function is used to convert an expression of one data type to another. The
CAST function is used to convert a data type without a specific format.
Syntax – CAST(Coulmn_Name, Data_Type)
SELECT CAST(1234, INT)
CONVERT() – This function is used to convert and formatting data types at the same time.
Syntax – CONVERT(Data_Type ,Coulmn_Name, Format_TYPE)
SELECT CONVERT(Varchar,GateDate(), 111) FROM Table_Name
SUBSTR() – This function
extracts a substring from a string (starting at any position).
Syntax – SELECT SUBSTRING(‘ABCDEF', 1, 3)
STUFF() – The STUFF function
inserts a string into another string. It deletes a specified length of
characters in the first string at the start position.
Syntax – SELECT STUFF('ABCDEF', 2, 3, 'IJKL')
REPLACE() – This function is used to replace an existing specified string value with another string value
of all occurrence.
Syntax – SELECT REPLACE('ABCDEFGHI','EFG','LMN')
RTRIM() – This function removes trailing spaces from a string from the right side.
Syntax – SELECT RTRIM(' ABCDEF ')
LTRIM() - This
function removes trailing spaces from a string from the left side.
Syntax – SELECT LTRIM(' ABCDEF ')
LEFT() – This Function extracts a number of characters from a string (starting from left)
Syntax – SELECT LTRIM('ABCDEF', 3)
RIGHT() – This Function extracts a number of characters from a string (starting from right).
Syntax – SELECT RIGHT('ABCDEF',3)
CHARINDEX() – This Function is used to searches for a substring in a string and returns the position.
Syntax – SELECT
(CHARINDEX('CDEF', 'ABCDEFGH'))
PATINDEX() – This Function is used to get starting position of a pattern by using wild characters in
the pattern.
Syntax – SELECT
(PATINDEX('%CDEF%', 'ABCDEFGH'))
ISNULL() – This function
is used to specify how we want to treat NULL values.
Syntax – SELECT ISNULL(Column_Name, Specified_value) FROM Table_Name
COALESCE() – This function is used to handle null value
from multiple arguments, and always returns first non-null value.
Syntax – SELECT COALESCE( NULL, ‘ABC’, ‘DEF’)
NVL() – This Function converts a null value to an actual value. Data types that can be used
are date, character, and number.
Syntax – SELECT NVL(ColumnValue, 0)
UCASE() – This function is used to convert the
column value into upper case.
Syntax – SELECT UCASE(Column_Name) FROM Table_Name
LCASE() – This function is used to convert the
column value into lower case.
Syntax – SELECT LCASE(Column_Name) FROM Table_Name
MID() – This Function extracts a substring from a string (starting at any position).
Syntax – SELECT MID(‘ABCDEFGH’, 4, 3)
LEN() – This function calculates the number of characters of an input string.
Syntax – SELECT LEN(‘ABCDEFGH’)
ROUND() – This Function rounds a number to a specified number of decimal places.
Syntax – SELECT ROUND (12345.6789, 2)
AVG() – This function
returns the average value of a numeric column.
Syntax – SELECT
AVG(Column_Name) FROM Table_Name
COUNT() – This function returns the number of rows.
Syntax – SELECT
COUNT(Column_Name) FROM Table_Name
FIRST() – This function
returns the first value of the selected column.
Syntax – SELECT FIRST(Column_Name) FROM Table_Name
LAST() – This
function returns the first value of the selected column.
Syntax – SELECT FIRST(Column_Name) FROM Table_Name
MAX() – This function returns the maximum value of the
selected column.
Syntax – SELECT MAX(Column_Name) FROM Table_Name
MIN() – This function returns the minimum value of the
selected column.
Syntax – SELECT MIN(Column_Name) FROM Table_Name
SUM() – This function returns aggregated sum value
of the selected column.
Syntax – SELECT SUM(Column_Name) FROM Table_Name
LEG() – Lead and Leg are called WINDOW functions
as well, are used to get preceding
and succeeding value of any row within its partition. The LAG() function is used
to get value from the row that precedes the current row.
Syntax – Select LAG(sale_value) OVER(ORDER BY sale_value) as previous_sale_value From SalesTable
LEAD() – The LEAD() function is used to get value from row that
succedes the current row.
Syntax – Select LEAD(sale_value) OVER(ORDER BY sale_value)
as Next_sale_value From SalesTable
RANK() – Ranking
functions return a ranking value for each row in a partition.
Syntax – Select RANK() OVER(PARTITION BY Column_Name ORDER BY Column_Name DESC) Rank From Table_Name
ROW_NUMBER() – This
function to get a unique sequential number for each row in the specified data.
Syntax – Select ROW_NUMBER() OVER(ORDER BY Column_Name) RowNumber From Table_Name
DENSE_RANK() – This function
to specify a unique rank number within the partition as per the specified
column value.
Syntax – Select DENSE_RANK() OVER(ORDER BY Column_Name) DenseRank From Table_Name
DATEDIFF() – This function returns the difference
between two dates based on argument value for Day, Month, and Year.
Syntax – SELECT
DATEDIFF(YEAR, ‘01/01/2011’, ‘01/01/2021, )
DATEADD() – This Function adds a time/date interval to date and then returns the date.
Syntax – SELECT DATEADD(year, 1, '01/01/2021’)
DATEPART() – This function returns an integer representing the specified datepart of
the specified date.
Syntax – SELECT DATEPART(year, '01/01/2021’)
DATENAME() – This function returns a character string representing the specified datepart of
the specified date
Syntax – SELECT DATENAME(Month, '01/01/2021’)
GETDATE() – This function returns the current date and time
of the system/server, in a 'YYYY-MM-DD
hh:mm:ss.mmm' format.
Syntax –
SELECT GETDATE()
CURRENT_TIMESTAMP() – This Function also returns the current date and time, in a 'YYYY-MM-DD hh:mm:ss.mmm' format.
Syntax – Select CURRENT_TIMESTAMP
FORMAT() – This function returns a value formatted with the specified format and optional culture,
mostly used for date format.
Syntax – SELECT FORMAT(‘01/01/2021’,
'd', 'en-US' ) 'US English'
SYSTEM_USER – This Function is used to return the current username.
Syntax – Select SYSTEM_USER
