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]