Function Execution in SQL Server 2005

In this article you will learn, everything about using Function Execution in SQL Server 2005

• String Functions
• Date and Time Functions
• Mathematical Functions

String FunctionsString Functions are used for manipulating string expression. Note: string expression should be passed within single quote.

• Len('') - Returns length of string.
• Example: select Len("Shri Ganesh") will return 11
• Lower('') - Convert all characters to lowercase characters.
• Example: select Lower('Shri Ganesh') will return shri ganesh
• Upper('') - Convert all characters to uppercase characters.
• Example: select Upper('Shri Ganesh') will return SHRI GANESH
• LTrim('') - Removes spaces from given character strings on left.
• Example: select LTrim(' Shri Ganesh') will return Shri Ganesh
• Note: It doesn't removes tab or line feed character.
• RTrim('') - Removes space from given character strings on right.
• Example: select LTrim('Shri Ganesh ') will return Shri Ganesh
• Note: It doesn't removes tab or line feed character.
• Trim('') - Removes spaces from given character strings from both left and right.
• Example: select LTrim(' Shri Ganesh ') will return Shri Ganesh
• Note: It doesn't removes tab or line feed character.
• SubString('') - Returns a part of string from original string.
• SubString(character_expression, position, length)
• position - specifies where the substring begins.
• length - specifies the length of the substring as number of characters.
• Example: select SubString('Shri Ganesh',6,7) where in
• 6 - Starting position of sub string from given string.
• 6 - It is no. of characters to be extract from given string, starting from 6.
• That is it will return "Ganesh" As ganesh start from 6th character upto 6 characters.
• Replace('') - Replace the desired string within the original string.
• Replace(character_expression, searchstring, replacementstring)
• SearchString - string which you want to replace.
• ReplaceString - new string which you want to replace with
• Example: select replace('Think High To Achieve High','High','Low')
• here, function search for every occurrence of High and replace it with Low.
• Original - Think High To Achieve High
• Result - Think Low To Achieve Low
• Right('') - extract particular characters from right part of given string expression.
• Example: select right('Think High To Achieve High',15) will return "To Achieve High"
• This function will be helpful when you want particular characters from right part.
• Example: Let say i have social security nos. and i want to extract last 4 digit of it.
• select right('111-11-1111',4) will return 1111
  select right('222-22-2222',4) will return 2222
  select right('333-33-3333',4) will return 3333
  select right('444-44-4444',4) will return 4444

Date and Time FunctionsDate and Time Functions are used for manipulating Date and Time expression.

• GetDate() - Returns current date and time of a system.
• Example: select GetDate() will return something like "2007-10-10 15:34:37.287"
• GetUTCDate() - Returns current date and time information as per UTC (Universal Time Coordinate or Greenwich Mean Time)
• Example: select GetDate() will return something like "2007-10-10 15:34:37.287"

DatePart and Abbrevation, which we will be using with DatePart, DateADD, DateDIFF function.

Datepart	Abbreviations
Year	yy, yyyy
Quarter	qq, q
Month	mm, m
Dayofyear	dy, y
Day	dd, d
Week	wk, ww
Weekday	dw, w
Hour	Hh
Minute	mi, n
Second	ss, s
Millisecond	Ms

• DatePart() - Returns an integer representing a datepart of a date.
• Note: Example are based on considering "2007-10-10 15:34:37.287" as GetDate()
• Example:
• select DatePart("day",GetDate()) will return 10.
• select DatePart("hour",GetDate()) will return 16.
• select DatePart("dayofyear",GetDate()) will return 283. And so on...
• DateADD() - Returns adds a date or time interval to a specified date.
• Syntax: DateADD(Abbrevation, number to be added, date)
• Example:
• select DateAdd("day",7,GetDate()) will return 2007-10-17 16:09:18.280
• select DateAdd("month",20,GetDate()) will return 2009-06-10 16:10:02.643
• And so on...
• DateDIFF() - Returns difference between two specified dates.
• Syntax: DateDIFF(Abbrevation, startdate, enddate)
• Note: If the end date is earlier than the start date, the function returns a negative number. If the start and end dates are equal or fall within the same interval, the function returns zero.
• Example:
• select DATEDIFF("mm", Getdate()-500,GETDATE()) will return 17
• You must pass valid start and end date otherwise you will receive error.

Mathematical Functions
Mathematical Functions are used for manipulating Mathematical expression.

• ABS() - Returns positive value of numeric expression.
• Example: In following example both statement will return 3.14
• select ABS(3.14)
  select ABS(-3.14)
• Ceiling() - Returns the smallest integer that is greater than or equal to a numeric expression.
• Example:
• select Ceiling(3.14) will return 4
  select Ceiling(-3.14) will return 3.
• Floor() -Returns the largest integer that is less than or equal to a numeric expression.
• Example:
• select Floor(3.14) will return 3
  select Floor(-3.14) will return 4
• Round() - Returns a numeric expression that is rounded to the specified length or precision.
• Example:
• select Round(3.14, 1) will return 3.10
  select Round(-3.17, 1) will return -3.20
• select Round(3.12345, 4) will return 3.12350
• select Round(3.12345, 3) will return 3.12300
• Power() - POWER(numeric_expression, power)
• Example: select power(2,3) will return 8