Wednesday, October 10, 2007

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 Functions
String 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 Functions
Date 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

No comments:

Most Recent Post

Community Updates

Subscribe Blog via Email

Enter your email address:



Disclaimers:We have tried hard to provide accurate information, as a user, you agree that you bear sole responsibility for your own decisions to use any programs, documents, source code, tips, articles or any other information provided on this Blog.
Page copy protected against web site content infringement by Copyscape