Functions in SQL Server

Here, we will have a look at how to use functions in SQL Server. 

REVERSE()

This function returns a character expression in reverse order.

Example

SELECT REVERSE(‘HSEKAR’)

Result
 
RAKESH
 
LTRIM()
 
This functions returns a character expression after removing all its leading blanks.
 
Example

SELECTLTRIM('   RAKESH')

Output
 

RAKESH
 
RTRIM()
 
This funciton returns a character expression after removing all its trailing blanks.
 
Example

SELECTRTRIM('RAKESH    ')

Output
 
RAKESH
 
REPLACE()
 
This function returns a string after removing all the instances of a substring replaced by another substring.
 
SYNTAX

REPLACE(MAINSTRING,STRING_TO_BE_REPLACED,REPLACED_WITH)

MAINSTRING                     
 
It is the String that contains the complete string part of which is to be replaced.
 
STRING_TO_BE_REPLACED
 
It specifies the substring to replace.
 
REPLACED_WITH
 
Specifies the substring with which to replace the located substring.
 
Example
 
SELECTREPLACE('MY NAME IS _','_','RAKESH SINGH'
 
Result
 
MY NAME IS RAKESH SINGH  
 
LEN

 
Len is a function that returns the length of a string. This function excludes trailing blank spaces. 

SELECTLEN('ALICE IN WODERLAND')

Output
 
18

SELECTLEN('ALICE IN WODERLAND      ')

Output
 
18

SELECTLEN('    ALICE IN WODERLAND      ')

Output
 
22
 
SUBSTRING
 
The Substring function returns the part of the string from a given expression.
 
SYNTAX

SUBSTRING(expression, Starting_Point,length)

SELECT SUBSTRING('ALICE IN WONDERLAND', 6, 14)

Output

IN WONDERLAND

LEFT()
 
This function returns the left most characters of a string on the basis of the length specified.
 
SYNTAX

LEFT(STRING,LENGTH)
 
STRING Specifies the string from which to get the left most characters.
 
LENGTH Specifies the number of characters to obtain.
 
Example

SELECT LEFT('RAKESHSINGH',6)

Output
 
'SINGH'
 
RIGHT()
 
This function returns the right most characters of a string of which the length is specified.
 
SYNTAX

 
RIGHT(STRING, LENGTH)
 
STRING Specifies the string from which to get the left-most characters.
 
LENGTH Specifies the number of characters to obtain.
 
Example

SELECT RIGHT('RAKESHSINGH',6)
 
ASCII()
 
This function returns the ASCII code value of the leftmost character of a character expression.
 
SYNTAX
 
ASCII(Expression_in_CHAR_or_VARCHAR )

Parameter

Expression_in_CHAR_or_VARCHAR: It can be of type CHAR or varchar.

Returns:INT

Example

SELECT ASCII('A')

OUTPUT

65

SELECT ASCII('AB')

OUTPUT

65
 
CHAR()
 
This function converts an INTASCII code to a corresponding character.
 
SYNTAX

 
CHAR(INT)
 
Parameter: It is an INTEGER in the range from 0 to 255. This function returns NULL if the integer is not in this range.
 
Returns:character
 
Example

SELECTCHAR(65)

Output

65

GETDATE()
 
GETDATE() is a function that returns the system's current date time.
 
Example

Declare @Date smalldatetime
set @Date =(SELECTGETDATE()); 
SELECT @Date

Result
 
Aug 15 2009  9:04PM

DATEADD()
 
DATEADD() adds or subtracts a datetime to a new datetime. It returns a datetime on the basis of addition or subtraction.
 
SYNTAX

DATEADD(DATEPART,VALUE,DATE)
 
DATEPART is the parameter that specifies the part of the date to be added or subtracted. The value parameter is used to increment the date part.
 
Example

Declare @Date datetime

set @Date =(SELECTGETDATE());

SELECT  @Date

 

SELECTDATEADD(DD, 5,@Date )AS TIME_AFTER_ADDITION

Output

Aug 15 2009 9:19 PM
 
New value after addition:
 
TIME_AFTER_ADDITION
2009-08-20 21:19:15.170
 
DATEPART()
 
DATEPART() is used when we need a part of date or time from a datetime.
 
SYNTAX

DATEPART(datepart,date)
 
Example

SELECTDATEPART(YEAR,GETDATE())AS'Year_Of_Datetime'

-           ToGet Only Month

SELECTDATEPART(MONTH,GETDATE())AS'Month_Of_Datetime'

-           ToGet Only Day

SELECTDATEPART(DAY,GETDATE())AS'Day_Of_Datetime'

-           ToGet Only hour

SELECTDATEPART(HOUR,GETDATE())AS'Hour_Of_Datetime'

Output

Year_Of_Datetime
2013
Month_Of_Datetime
3
Day_Of_Datetime
31
Hour_Of_Datetime
10
 
DATEDIFF()
 
DATEDIFF() is a very common function to determine the difference between two DateTimes.
 
SYNTAX
 
DATEDIFF(datepart, startdate, enddate)

Example

Declare @FirstDate datetime

 

Declare @SecondDate datetime

 

set @FirstDate =(SELECTGETDATE());

 

set @SecondDate =(SELECTDATEADD(DD, 5,@FirstDate))

 

SELECTDATEDIFF(DD, @FirstDate, @SecondDate)AS Difference_Of_Day

Output

Difference_Of_Day
10
 
DATENAME()

DATENAME() is a function to determine the date name from the datetimevalue.

Example

-- Get Today

SELECTDATENAME(DW,GETDATE())AS'TODAY IS'

-- Get Month name

SELECTDATENAME(MM,getdate())AS'Current Month'

Output

Today Is
Sunday
Current Month
March
 
DAY()
 
DAY() is used to get the day from any date time object.

Example

SELECTDAY(GETDATE())AS'TODAY IS'

 Output
 
TODAY IS
31
 
MONTH()

SELECTMONTH(GETDATE())AS'CURRENT MONTH'

Output

CURRENTMONTH
3
 
YEAR()

SELECTYEAR(GETDATE())AS'CURRENT YEAR'

Output
 
'CURRENT YEAR'
2013
 
CHARINDEX
 
CharIndex returns the first occurance of a string or characters within another string. The Format of CharIndex is given below:
 
CHARINDEX ( expression1 , expression2 [ , start_location ] )

Here expression1 is the string of characters to be found within expression2. So if you want to search ij within the word Abhijit, we will use ij as expression1 and Abhijit as expression2. start_location is an optional integer argument that identifies the position from where the string will be searched. Now let us look at some examples.
 
SELECT CHARINDEX('SQL', 'Microsoft SQL Server')

RESULT
 
11
 
So it will start from 1 and go on searching until it finds the total string element searched, and returns its first position. The Result will be 0 if the searched string is not found.
 
We can also mention the Start_Location of the string to be searched.
 
EXAMPLE
 
SELECT CHARINDEX('SQL', 'Microsoft SQL server has a great SQL Engine',12)

So in the preceding example we can have the Output as 34 as we specified the StartLocation as 12, that is greater than initial SQL position(11).
 
PATINDEX

 
As a contrast PatIndex is used to search a pattern within an expression. The Difference between CharIndex and PatIndex is the later allows WildCard Characters.
 
PATINDEX ( '%pattern%' , expression)
 
Here the first argument takes a pattern with wildcard characters like '%' (meaning any string) or '_' (meaning any character).
 
For Example

PATINDEX('%BC%','ABCD')
 
Result
 
2
 
Another flexibility of PATINDEX is that you can specify a number of characters allowed within the Pattern. Say you want to find all of the records that contain the words "Bread", or "bread" in a string, You can use the following :
 
SELECT PATINDEX('%[b,B]read%', 'Tommy loves Bread')

In this example, we mentioned both b and B in square brackets. The Result will be 13 that is same if we have searched in 'Tommy loves bread'.
 
STUFF
 
Stuff is another SQL Function that is used to delete a specified length of characters within a string and replace with another set of characters. The general SYNTAX: of STUFF is as below.
 
STUFF(character_expression1, start, length, character_expression2)Character_Expression1 represents the string in which the stuff is to be applied. start indicates the starting position of the character in character_expression1, length is the length of characters that need to be replaced. character_expression2 is the string that will be replaced to the start position.
 
The following is an example:
 
SELECT STUFF('SQL SERVER is USEFUL',5,6,'DATABASE')
 
So the result will be:

SQL DATABASE is USEFUL
 
LOWER / UPPER
 
Some other simple but handy functions are Lower / UPPER. They will just the change case of a string expression. For example:
 
SELECT UPPER('this is Lower TEXT')
 
Result

THIS IS LOWER TEXT
 
QUOTNAME()
 
Returns a Unicode string with the delimiters added to make the input string a valid Microsoft SQLServer delimited identifier.
 
SYNTAX
 
QUOTENAME('character_string' [ , 'quote_character' ] )

Arguments

' character_string '
Is a string ofUnicode character data. character_string issysnameand is limited to 128 characters. Inputs greater than 128 characters return NULL.
 
' quote_character '
Is a one-character string to use as the delimiter. Can be a single quotation mark('), a left or right bracket( [ ] ), or a double quotation mark( " ). If quote_character is not specified, brackets are used.
 
Return Types: nvarchar(258)
 
Examples

The following example takes the character string abc[]def and uses the [ and ] characters to create a valid SQL Server delimited identifier.
 
SELECT QUOTENAME('abc[]def')

Result
 
[abc[]]def]