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 | 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:
Post a Comment