Click here to Skip to main content
16,017,857 members
Articles / All Topics

Most Commonly Used Functions in SQL Server 2005/2008

,
Rate me:
Please Sign up or sign in to vote.
4.71/5 (60 votes)
6 Aug 2012CPOL8 min read 361.8K   140   50
This Table of Contents and articles is editable by all Silver members and above. Please update the article with as many as function you aware in SQL Server 2005/2008 that we are used generally. This will put all of them under a single article.
Objective of this article is to put all mostly used function related in SQL Server 2005/2008 under a sinlge article. There are several function that we are used regularly in SQL Server 2005/2008. This article is will a common place for all those function with proper example.

But, I need your help. This Table of Contents and Article is editable by all Silver members and above. What I want you to do is replace the entries in the Table of Contents below add as many as function you aware on SQL Server 2005 or above. This will really help beginners to find out all of them under a single article.

Table of Contents

Objective

Objective of this article is to put all mostly used function related in SQL Server 2005. There are several function that we are used regularly in SQL Server 2005. This article is will a common place for all those function with proper example.

But, I need your help. This Table of Contents and Article is editable by all Silver members and above. What I want you to do is replace the entries in the Table of Contents below add as many as function you aware on SQL Server 2005 or above. This will really help beginners to find out all of them under a single article.

I am starting with few function related to DateTime function. I will update the article regular manner, but I expect a major contribution from you guys. Please don't forget to update the History list with your name and code project profile link.

Thanks in advance for supporting my one small initiative.

DateTime Function in SQL Server

Below are the most commonly used DateTime function in SQL Server.

  • GETDATE()
  • DATEADD()
  • DATEPART()
  • DATEDIFF()
  • DATENAME()
  • DAY()
  • MONTH()
  • YEAR()

GETDATE()

GETDATE() is very common used method which returns exact date time from the system. It does not accept any parameter. Just call it like simple function.

Example :

Declare @Date datetime 
set @Date = (SELECT GETDATE());
Print @Date 

OutPut:

Aug 15 2009  9:04PM 

DATEADD()

DATEADD() is used to add or subtract datetime. Its return a new datetime based on the added or subtracted interval.

General Syntax

DATEADD(datepart, number, date)

datepart is the parameter that specifies on which part of the date to return a new value. Number parameter is used to increment datepart.

Example :

Declare @Date datetime 
set @Date = (SELECT GETDATE());
print  @Date -- Print Current Date
-- Adding 5 days with Current Date
SELECT DATEADD(day, 5,@Date ) AS NewTime

Output :

Aug 15 2009  9:19PM
NewTime
-----------------------
2009-08-20 21:19:15.170

DATEPART()

DATEPART() is used when we need a part of date or time from a datetime variable. We can use DATEPART() method only with select command.

Syntax

DATEPART(datepart, date)

Example :

-- Get Only Year
SELECT DATEPART(year, GETDATE()) AS 'Year'
-- Get Only Month
SELECT DATEPART(month, GETDATE()) AS 'Month'
-- Get Only hour
SELECT DATEPART(hour, GETDATE()) AS 'Hour

Output :

Year
-----------
2009
Month
-----------
8
Hour
-----------
21

DATEDIFF()

DATEDIFF() is very common function to find out the difference between two DateTime elements.

Syntax

DATEDIFF(datepart, startdate, enddate)

Example :

-- Declare Two DateTime Variable
Declare @Date1 datetime 
Declare @Date2 datetime 
-- Set @Date1 with Current Date
set @Date1 = (SELECT GETDATE());
-- Set @Date2 with 5 days more than @Date1
set @Date2 = (SELECT DATEADD(day, 5,@Date1 ))
-- Get The Date Difference
SELECT DATEDIFF(day, @Date1, @Date2) AS DifferenceOfDay

Output :

DifferenceOfDay
---------------
5 

DATENAME()

DATENAME() is very common and most useful function to find out the date name from the datetime value.

Example

-- Get Today 
SELECT DATENAME(dw, getdate()) AS 'Today Is'
-- Get Mont name
SELECT DATENAME(month, getdate()) AS 'Month'

Output :

Today Is
------------------------------
Saturday
Month
------------------------------
August

DAY()

DAY() is used to get the day from any date time object.

Example:

SELECT DAY(getdate()) AS 'DAY'

Output :

DAY
-----------
15

MONTH()

SELECT MONTH(getdate()) AS 'Month'

Output :

Month
-----------
8

YEAR()

SELECT YEAR(getdate()) AS 'Year'

Output :

Year
-----------
2009

String Functions

Some of the String Functions comes very handy at times. Let us discuss them one by one.

ASCII()

Returns the ASCII code value of the leftmost character of a character expression.

Syntax
SQL
ASCII ( character_expression ) 

Arguments: character_expression : Is an expression of the type char or varchar.

Return Types: Int 

Example:  

SQL
SELECT ASCII('A')  

SET TEXTSIZE 0
SET NOCOUNT ON
-- Create the variables for the current character string position 
-- and for the character string.
DECLARE @position int, @string char(15)
-- Initialize the variables.
SET @position = 1
SET @string = 'The codeProject'
WHILE @position <= DATALENGTH(@string)
   BEGIN
   SELECT ASCII(SUBSTRING(@string, @position, 1)),
      CHAR(ASCII(SUBSTRING(@string, @position, 1)))
    SET @position = @position + 1
   END
SET NOCOUNT OFF

Output:

SQL
-----------
65
----------- ----
84          T
----------- ----
104         h
----------- ----
101         e
----------- ----
and so on..... 
CHAR()  

Converts an int ASCII code to a character.  

Syntax  
SQL
CHAR ( integer_expression ) 

Arguments: integer_expression: Is an integer from 0 through 255. NULL is returned if the integer expression is not in this range.
Return Types:  character  
Example:  

SQL
SET TEXTSIZE 0
SET NOCOUNT ON
DECLARE @intCounter int
SET @intCounter = 0

WHILE (@intCounter<= 255)
	BEGIN
		SELECT 'CHAR - ' + CHAR(@intCounter) + '. ASCII - ' + CONVERT(VARCHAR,@intCounter) 
		SET @intCounter = @intCounter + 1
	END


SET NOCOUNT OFF

Output:

SQL
CHAR - !. ASCII - 33
------------------------------------------------
CHAR - ". ASCII - 34
------------------------------------------------
CHAR - #. ASCII - 35
------------------------------------------------
CHAR - $. ASCII - 36
------------------------------------------------
CHAR - %. ASCII - 37
------------------------------------------------
CHAR - &. ASCII - 38
------------------------------------------------
CHAR - '. ASCII - 39
------------------------------------------------
CHAR - (. ASCII - 40
------------------------------------------------

and so on.....   

NCHAR() 

Return a unicode character representing a number passed as a parameter.

Syntax 
SQL
NCHAR ( integer_expression )  

Return Types:  character 

Example : 

SQL
SELECT NCHAR(97

OutPut

SQL
This will return the leter "a"

DIFFERENCE()

Returns an integer value that indicates the difference between the SOUNDEX values of two character expressions.

Syntax
SQL
DIFFERENCE ( character_expression , character_expression )

Arguments:character_expression: Is an expression of type char or varchar. character_expression can also be of type text; however, only the first 8,000 bytes are significant.

Return Types:  Int

Example :

SQL
USE AdventureWorks;
GO
-- Returns a DIFFERENCE value of 4, the least possible difference.
SELECT SOUNDEX('Green'), SOUNDEX('Greene'), DIFFERENCE('Green','Greene');
GO
-- Returns a DIFFERENCE value of 0, the highest possible difference.
SELECT SOUNDEX('Blotchet-Halls'), SOUNDEX('Greene'), DIFFERENCE('Blotchet-Halls', 'Greene');
GO

Output:

SQL
----- ----- ----------- 
G650  G650  4           

(1 row(s) affected)
                        
----- ----- ----------- 
B432  G650  0           

(1 row(s) affected)

LEFT() 

Returns the left most characters of a string.

Syntax
SQL
LEFT(string, length

string
Specifies the string from which to obtain the left-most characters.
 
length
Specifies the number of characters to obtain.

Example :

SQL
SELECT LEFT('Marufuzzaman',5)  

OutPut

SQL
Maruf

RIGHT()

Returns the right most characters of a string.

Syntax  
SQL
RIGHT(string, length

string
Specifies the string from which to obtain the left-most characters.
 
length
Specifies the number of characters to obtain.

Example :

SQL
SELECT RIGHT('Md. Marufuzzaman',12)  

OutPut

SQL
Marufuzzaman 

LTRIM() 

Returns a character expression after it removes leading blanks.

Example :

SQL
SELECT LTRIM('   Md. Marufuzzaman') 

OutPut

SQL
Md. Marufuzzaman

RTRIM()

Returns a character string after truncating all trailing blanks. 

Example : 

SQL
SELECT RTRIM('Md. Marufuzzaman    ') 

OutPut

SQL
Md. Marufuzzaman

REPLACE() 

Returns a string with all the instances of a substring replaced by another substring.

Syntax
SQL
REPLACE(find, replace, string)
Find
Specifies the string that contains the substring to replace all instances of with another.
 
Replace
Specifies the substring to locate.
 
String
Specifies the substring with which to replace the located substring.
 
Example :
SQL
SELECT REPLACE('The codeProject is ?.','?', 'your development resource')

OutPut:

SQL
The codeProject is your development resource.

QUOTNAME()

Returns a Unicode string with the delimiters added to make the input string a valid Microsoft SQL Server delimited identifier.

Syntax
SQL
QUOTENAME ( 'character_string' [ , 'quote_character' ] )  
Arguments
' character_string '
Is a string of Unicode character data. character_string is sysname and 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.

SQL
SELECT QUOTENAME('abc[]def')
OutPut:
[abc[]]def] 

REVERSE()

Returns a character expression in reverse order. 

Example :

SELECT REVERSE('namazzufuraM .dM')

Output: 

Md. Marufuzzaman 

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 which identifies the position from where the string will be searched. Now let us look into some examples :

SELECT CHARINDEX('SQL', 'Microsoft SQL Server') 

OUTPUT:

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 above example we can have the Output as 34 as we specified the StartLocation as 12, which 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')

Output:  

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 which is same if we have searched in 'Tommy loves bread'.

LEN

Len is a function which returns the length of a string. This is the most common and simplest function that everyone use. Len Function excludes trailing blank spaces.

SELECT LEN('ABHISHEK IS WRITING THIS')

This will output 24, it is same when we write LEN('ABHISHEK IS WRITING THIS ') as LEN doesnt take trailing spaces in count.

STUFF


Stuff is another TSql Function which 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 which need to be replaced. character_expression2 is the string that will be replaced to the start position.

Let us take an example :

SELECT STUFF('SQL SERVER is USEFUL',5,6,'DATABASE')

So the result will be :
SQL DATABASE is USEFUL

SUBSTRING


Substring returns the part of the string from a given characterexpression. The general syntax of Substring is as follows :

SUBSTRING(expression, start, length)

Here the function gets the string from start to length. Let us take an example below:

SELECT OUT = SUBSTRING('abcdefgh', 2, 3)

The output will be "bcd".
Note : substring also works on ntext, VARCHAR, CHAR etc.

LOWER / UPPER


Anoter simple but handy function is Lower / UPPER. The will just change case of a string expression. For Example,

SELECT UPPER('this is Lower TEXT') 


Output:
THIS IS LOWER TEXT

Message to All Silver Member and Above

This Table of Contents and Article is editable by all Silver members and above. What I want you to do is replace the entries in the Table of Contents add as many as function you aware on SQL Server 2005 or above. This will really help beginners to find out all of them under a single article.

Thanks in advance for supporting my small initiative.

History

Draft Version Posted : 15th Aug. 09 - Abhijit Jana

Second Version: 16th Aug. 09 - Abhishek Sur
Updates: Added String Functions.  

Third Version: 18th Aug. 09 - Md. Marufuzzaman
Updates: Added some more useful String Functions. 

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
President
India India
Did you like his post?

Oh, lets go a bit further to know him better.
Visit his Website : www.abhisheksur.com to know more about Abhishek.

Abhishek also authored a book on .NET 4.5 Features and recommends you to read it, you will learn a lot from it.
http://bit.ly/EXPERTCookBook

Basically he is from India, who loves to explore the .NET world. He loves to code and in his leisure you always find him talking about technical stuffs.

Working as a VP product of APPSeCONNECT, an integration platform of future, he does all sort of innovation around the product.

Have any problem? Write to him in his Forum.

You can also mail him directly to abhi2434@yahoo.com

Want a Coder like him for your project?
Drop him a mail to contact@abhisheksur.com

Visit His Blog

Dotnet Tricks and Tips



Dont forget to vote or share your comments about his Writing

Written By
Technical Lead
India India
.NET Consultant | Former Microsoft MVP - ASP.NET | CodeProject MVP, Mentor, Insiders| Technology Evangelist | Author | Speaker | Geek | Blogger | Husband

Blog : http://abhijitjana.net
Web Site : http://dailydotnettips.com
Twitter : @AbhijitJana
My Kinect Book : Kinect for Windows SDK Programming Guide

Comments and Discussions

 
Questionsupport Pin
Member 1089896013-Apr-17 20:36
Member 1089896013-Apr-17 20:36 
QuestionSQL Functions Pin
HashimKhanBarak17-Aug-13 2:47
HashimKhanBarak17-Aug-13 2:47 
AnswerRe: SQL Functions Pin
Shah Sneha22-Nov-13 0:35
Shah Sneha22-Nov-13 0:35 
GeneralMy vote of 5 Pin
WhiteOsoBDN24-Apr-13 22:07
WhiteOsoBDN24-Apr-13 22:07 
GeneralMy vote of 2 Pin
Brett Phipps at MFA31-Oct-12 10:34
Brett Phipps at MFA31-Oct-12 10:34 
Generalyes most regular used functions!! Pin
Amit kumar pathak31-Aug-12 3:43
Amit kumar pathak31-Aug-12 3:43 
GeneralMy vote of 4 Pin
magicpapacy12-Aug-12 20:47
magicpapacy12-Aug-12 20:47 
GeneralMy vote of 5 Pin
ii_noname_ii7-Aug-12 3:48
ii_noname_ii7-Aug-12 3:48 
GeneralMy vote of 1 Pin
Bad code hunter6-Aug-12 19:33
Bad code hunter6-Aug-12 19:33 
GeneralRe: My vote of 1 Pin
Abhishek Sur15-Aug-12 12:07
professionalAbhishek Sur15-Aug-12 12:07 
GeneralMy vote of 1 Pin
SalCon6-Aug-12 15:00
SalCon6-Aug-12 15:00 
GeneralMy vote of 5 Pin
_Tushar Patil20-Mar-12 20:54
_Tushar Patil20-Mar-12 20:54 
Fantastic Article keep It Up........
GeneralMy vote of 3 Pin
candan27-Dec-11 21:14
professionalcandan27-Dec-11 21:14 
GeneralMy vote of 5 Pin
AMEL HUSIC1-Sep-11 3:44
AMEL HUSIC1-Sep-11 3:44 
GeneralHi Companion Pin
Anil Srivastava22-Mar-11 2:59
Anil Srivastava22-Mar-11 2:59 
GeneralMy vote of 5 Pin
Sandesh M Patil21-Dec-10 3:59
Sandesh M Patil21-Dec-10 3:59 
GeneralMy vote of 5 Pin
thatraja6-Oct-10 21:13
professionalthatraja6-Oct-10 21:13 
GeneralMy vote of 5 Pin
Sharad Mishra27-Sep-10 23:02
Sharad Mishra27-Sep-10 23:02 
GeneralExcellent article Pin
kbui4-Sep-10 13:11
kbui4-Sep-10 13:11 
GeneralRe: Excellent article Pin
Abhijit Jana12-Sep-10 18:10
professionalAbhijit Jana12-Sep-10 18:10 
GeneralWell Done! Pin
Roger Wright21-Nov-09 7:16
professionalRoger Wright21-Nov-09 7:16 
GeneralRe: Well Done! Pin
Abhijit Jana21-Nov-09 20:08
professionalAbhijit Jana21-Nov-09 20:08 
GeneralNice... Pin
Shivprasad koirala25-Aug-09 7:27
Shivprasad koirala25-Aug-09 7:27 
GeneralRe: Nice... Pin
Abhijit Jana25-Aug-09 7:51
professionalAbhijit Jana25-Aug-09 7:51 
GeneralAnother Stuff To Be Added Pin
Sundeep Ganiga20-Aug-09 20:22
Sundeep Ganiga20-Aug-09 20:22 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.