SQL Server 2005/2008 Functions

Most Commonly Used Functions in SQL Server 2005/2008

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.

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

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

Example: 

SELECT ASCII('A') 

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

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:

-----------

65
----------- ----
84          T
----------- ----
104         h
----------- ----
101         e
----------- ----
and so on..... 

CHAR()  

Converts an int ASCII code to a character.  

Syntax  

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: 

DECLARE @intCounter int

SET @intCounter = 0

WHILE (@intCounter<= 255)

 BEGIN

   SELECT 'CHAR - '+CHAR(@intCounter)+'. ASCII - '+CONVERT(VARCHAR,@intCounter)

       SET @intCounter = @intCounter + 1

 END

Output

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.

Example :

SELECT NCHAR(97)

OutPut

This will return the leter "a"

LEFT()
Returns the left most characters of a string.

Syntax
LEFT(string, length)

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

Example :
SELECT LEFT('Marufuzzaman',5) 

OutPut
Maruf

RIGHT()
Returns the right most characters of a string.

Syntax 
RIGHT(string, length)

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

Example :
SELECT RIGHT('Md. Marufuzzaman',12) 

OutPut
Marufuzzaman

LTRIM()
Returns a character expression after it removes leading blanks.

Example :
SELECT LTRIM('   Md. Marufuzzaman')

OutPut
Md. Marufuzzaman

RTRIM()
Returns a character string after truncating all trailing blanks.

Example :
SELECT RTRIM('Md. Marufuzzaman    ')

OutPut
Md. Marufuzzaman

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

Syntax
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 :
SELECT REPLACE('The softMindIT is ?.','?', 'your development resource')

OutPut:
The softMindIT is your development resource.

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

expression1 is a sequence of characters to be found from expression2. It is of short character data type.
expression2 is a string from which expression1 is searched. It is of character string data type. 

start_location is a position to start searching for expression1 in expression2. It is an optional field. If you don't specify or specify 0 or negative number the search starts from beginning of expression2.

It returns an integer value.

Example:

SELECT CHARINDEX('ax','Syntax-Example-Syntax',0)

Or

SELECT CHARINDEX('ax','Syntax-Example-Syntax')

Output
5

SELECT CHARINDEX('ax','Syntax-Example-Syntax',6)

Output
20

SELECT ContactName

FROM    Customers

WHERE  CHARINDEX('an',ContactName) > 0

Output

ContactName

Maria Anders

Ana Trujillo

Antonio Moreno

Hanna Moos

Above example displays all customer names having substring 'an' in customer name of customers table.

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

ROW_NUMBER ()-Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

Rank() - Returns the rank of each row within the partition of a result set.

RANK ( ) OVER ( [ partition_by_clause ] order_by_clause )

EX-

SELECT *, RANK() OVER(PARTITION BY type ORDER BY price) rnk FROM Printer;

DENSE_RANK () - Returns the rank of rows within the partition of a result set, without any gaps in the ranking.

STUFF-  The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.

STUFF ( character_expression , start , length , replaceWith_expression )

Example:

SELECT STUFF('abcdef', 2, 3, 'ijklmn');

GO

Here is the result set.

--------- 

aijklmnef 

(1 row(s) affected)

CONCAT

Returns a string that is the result of concatenating two or more string values.

SELECT CONCAT ( 'Happy ', 'Birthday ', 11, '/', '25' ) AS Result;

Here is the result set.

Result

-------------------------

Happy Birthday 11/25

(1 row(s) affected)

Left- Returns the left part of a character string with the specified number of characters.

SELECT LEFT('abcdefg',2)

GO

Here is the result set.

-- 

ab 

(1 row(s) affected)

PATINDEX-  Returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found, on all valid text and character data types.

PATINDEX ( '%pattern%' , expression )

SELECT PATINDEX('%en_ure%', 'please ensure the door is locked');

Here is the result set.

------------

8

Replace - Replaces all occurrences of a specified string value with another string value.

SELECT REPLACE('abcdefghicde','cde','xxx');

GO

Here is the result set.

------------

abxxxfghixxx

(1 row(s) affected)

 REPLICATE -

Repeats a string value a specified number of times.

REPLICATE ( string_expression ,integer_expression )

USE AdventureWorks2012;

GO

SELECT [Name]

, REPLICATE('0', 4) + [ProductLine] AS 'Line Code'

FROM [Production].[Product]

WHERE [ProductLine] = 'T'

ORDER BY [Name];

GO

Here is the result set.

Name                                               Line Code

-------------------------------------------------- ---------

HL Touring Frame - Blue, 46                        0000T 

HL Touring Frame - Blue, 50                        0000T 

HL Touring Frame - Blue, 54                        0000T 

HL Touring Frame - Blue, 60                        0000T 

HL Touring Frame - Yellow, 46                      0000T 

HL Touring Frame - Yellow, 50                      0000T

...

SPACE- Returns a string of repeated spaces.

USE AdventureWorks2012;

GO

SELECT RTRIM(LastName) + ',' + SPACE(2) +  LTRIM(FirstName)

FROM Person.Person

ORDER BY LastName, FirstName;

GO

STR Returns character data converted from numeric data.

STR ( float_expression [ , length [ , decimal ] ] )

SELECT STR(123.45, 6, 1);

GO

Here is the result set.

------

 123.5

(1 row(s) affected)

Convert Integer to String in SQL Server
A very frequently asked question is how to convert an Integer to String in SQL Server. Here are 3 different ways of doing the same task:

DECLARE @i int
SET @i=98235

--Method 1 : Use CAST function
SELECT CAST(@i as varchar(10))

--Method 2 : Use CONVERT function
SELECT CONVERT(varchar(10),@i)

--Method 3 : Use STR function
SELECT LTRIM(STR(@i,10))

CHOOSE (Transact-SQL)

Returns the item at the specified index from a list of values in SQL Server.

Syntax - CHOOSE ( index, val_1, val_2 [, val_n ] )

Examples
The following example returns the third item from the list of values that is provided.

SELECT CHOOSE ( 3, 'Manager', 'Director', 'Developer', 'Tester' ) AS Result;

Here is the result set.
Result
-------------
Developer

Thanks:

Suraj K Mad.