SQL Functions
Functions in SQL server can be broadly divided into 2 categoris
1. Built-in functions
2. User Defined functions
There are several built-in functions,we will look at the most common string functions available.
String Functions -
CHAR(Integer_Expression) - Converts an int ASCII code to a character. The Integer_Expression, should be between 0 and 255.
The following SQL, prints all the characters for the ASCII values from o thru 255
Declare @Number int
Set @Number = 1
While(@Number <= 255)
Begin
Print CHAR(@Number)
Set @Number = @Number + 1
End
Note: The while loop will become an infinite loop, if you forget to include the following line.
Set @Number = @Number + 1
Printing uppercase alphabets using CHAR() function:
Declare @Number int
Set @Number = 65
While(@Number <= 90)
Begin
Print CHAR(@Number)
Set @Number = @Number + 1
End
LTRIM(Character_Expression) - Removes blanks on the left handside of the given character expression.
Example: Removing the 3 white spaces on the left hand side of the ' Hello' string using LTRIM() function.
Select LTRIM(' Hello')
Output: Hello
RTRIM(Character_Expression) - Removes blanks on the right hand side of the given character expression.
Example: Removing the 3 white spaces on the left hand side of the 'Hello ' string using RTRIM() function.
Select RTRIM('Hello ')
Output: Hello
Example: To remove white spaces on either sides of the given character expression, use LTRIM() and RTRIM() as shown below.
Select LTRIM(RTRIM(' Hello '))
Output: Hello
LOWER(Character_Expression) - Converts all the characters in the given Character_Expression, to lowercase letters.
Example: Select LOWER('CONVERT This String Into Lower Case')
Output: convert this string into lower case
UPPER(Character_Expression) - Converts all the characters in the given Character_Expression, to uppercase letters.
Example: Select UPPER('CONVERT This String Into upper Case')
Output: CONVERT THIS STRING INTO UPPER CASE
REVERSE('Any_String_Expression') - Reverses all the characters in the given string expression.
Example: Select REVERSE('ABCDEFGHIJKLMNOPQRSTUVWXYZ')
Output: ZYXWVUTSRQPONMLKJIHGFEDCBA
LEN(String_Expression) - Returns the count of total characters, in the given string expression, excluding the blanks at the end of the expression.
Example: Select LEN('SQL Functions ')
Output: 13
1. Built-in functions
2. User Defined functions
There are several built-in functions,we will look at the most common string functions available.
Functions –
Function
accept only
input parameters, perform actions and return the result. A function can
return an only a single value or a table. We
can't use a function to Insert, Update, Delete records
in the database tables.
Note –some
functions need input parameters , some functions not required
Different Types of SQL Server & SQL Database Functions.-
1.Aggregate functions
2.Mathematical
functions
3.Conversation
functions
4.Null
functions
5.String
functions
(Text Functions)
6.Date
and
Time functions
7.Analytical functions -(Ranking Functions ,Window functions ,Pivot & Un pivot)
Aggregate
functions-
AVG –
calculates the average of a set of values.
COUNT –
counts rows in a specified table or view.
MIN –
gets the minimum value in a set of values.
MAX –
gets the maximum value in a set of values.
SUM –
calculates the sum of values.
Mathematical
functions –
ABS(X) -This function returns the absolute value of X.
For example:
Eg:- Select abs(-6);
MOD(X,Y) - The variable
X is divided by Y and their remainder is returned. Eg : - Select mod
(9,5);
SIGN(X)
-This method
returns 1 if X is positive, -1 if it is negative and 0 if the value of X is 0.
Eg – select SIGN
(10)
FLOOR(X)
-This returns
the Floor
value of integer value
that is either less than X or equal to it
It will accept
floating values and return integer values
eg -5.2
-- Returns 5
CEILING (X)
- function returns next round value
eg -5.2
-- Returns 6
Power (X
,Y) – Function returns Power value
Eg –
(2)2 =4 ,
(100)3 =
10,00,000
Square (X) –
function returns the square value - that
means Square place is assigned to 2, but power
function
we will pass the parameter
Eg=
2*2
=4
3* 2=9
SQUARE Root (SQRT) - this function hepls to Finding
the square
root of
a number
RAND ( )
- return random Decimal value each time
Conversation
functions-
cast - this function helps to convert one data type to another datatype eg -cast (9.5
as int)
Convert -
Eg SELECT CONVERT(DATETIME, '20190314') result
NULL
FUNCTION
ISNULL - Helps to replace the Null value
coalesce - Return first not null value
NULLIF – Required two parameters – both are equal
then return Null
other wise it will
return first
parameter value
String Functions -
ASCII(Character_Expression) - Returns the ASCII code of the given character expression.
To find the ACII Code of capital letter 'A'
Example: Select ASCII('A')
Output: 65
To find the ACII Code of capital letter 'A'
Example: Select ASCII('A')
Output: 65
CHAR(Integer_Expression) - Converts an int ASCII code to a character. The Integer_Expression, should be between 0 and 255.
The following SQL, prints all the characters for the ASCII values from o thru 255
Declare @Number int
Set @Number = 1
While(@Number <= 255)
Print CHAR(@Number)
Set @Number = @Number + 1
End
Note: The while loop will become an infinite loop, if you forget to include the following line.
Set @Number = @Number + 1
Printing uppercase alphabets using CHAR() function:
Declare @Number int
Set @Number = 65
While(@Number <= 90)
Begin
Print CHAR(@Number)
Set @Number = @Number + 1
End
LTRIM(Character_Expression) - Removes blanks on the left handside of the given character expression.
Example: Removing the 3 white spaces on the left hand side of the ' Hello' string using LTRIM() function.
Select LTRIM(' Hello')
Output: Hello
RTRIM(Character_Expression) - Removes blanks on the right hand side of the given character expression.
Example: Removing the 3 white spaces on the left hand side of the 'Hello ' string using RTRIM() function.
Select RTRIM('Hello ')
Output: Hello
Example: To remove white spaces on either sides of the given character expression, use LTRIM() and RTRIM() as shown below.
Select LTRIM(RTRIM(' Hello '))
Output: Hello
LOWER(Character_Expression) - Converts all the characters in the given Character_Expression, to lowercase letters.
Example: Select LOWER('CONVERT This String Into Lower Case')
Output: convert this string into lower case
UPPER(Character_Expression) - Converts all the characters in the given Character_Expression, to uppercase letters.
Example: Select UPPER('CONVERT This String Into upper Case')
Output: CONVERT THIS STRING INTO UPPER CASE
REVERSE('Any_String_Expression') - Reverses all the characters in the given string expression.
Example: Select REVERSE('ABCDEFGHIJKLMNOPQRSTUVWXYZ')
Output: ZYXWVUTSRQPONMLKJIHGFEDCBA
LEN(String_Expression) - Returns the count of total characters, in the given string expression, excluding the blanks at the end of the expression.
Example: Select LEN('SQL Functions ')
Output: 13
Comments