Most Commonly Used Functions in SQL Server 2005/2008

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.

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.

QUOTNAME()

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

Syntax

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.

REVERSE()

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).

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'.

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.

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.

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.

Presently he is working in WPF, a new foundation to UI development, but mostly he likes to work on architecture and business classes. ASP.NET is one of his strength as well.
Have any problem? Write to him in his Forum.