for RuBoard |

Hundreds of timesaving functions are built into Microsoft SQL Server. These functions enable you to perform all sorts of tasks such as working with dates and strings and performing mathematical calculations. Some of the most commonly used functions are described in this section. However, you can locate a list of all built-in functions by searching Microsoft SQL Server Books Online for "functions."

Microsoft SQL Server ships with a number of functions that enable you to manipulate strings. For the most part, these string functions are similar to the ones used in Microsoft Visual Basic.

For instance, the ` Left() ` and ` Right() ` functions are nearly identical to their counterparts. They enable you to return part of a character string, from either the left or right end of the string, respectively. They have the following function definitions:

Left( string, value ) Right( string, value )

By calling the ` Left() ` function, and passing in 'She sells sea shells ' as the string and 6 as the value, ` Left() ` returns 'She se'. Likewise, ` Right() ` with the same arguments returns 'shells'.

Sometimes, when working with strings, you need to convert the entire string to either uppercase or lowercase to compare two strings or to ensure that data is entered into a certain field in a standard way. The ` upper() ` and ` lower() ` methods perform exactly these tasks. The two methods accept the string to convert as a single argument.

Table 3.1 contains a list of some SQL string functions and their return values for a given string.

Function Definition | Return Value for String: ' Gaiking Space Robot ' |
---|---|

Len( string ) | 21 |

LTrim( string ) | 'Gaiking Space Robot ' |

RTrim( string ) | ' Gaiking Space Robot' |

Reverse( string ) | ' toboR ecapS gnikiaG ' |

Lower( string ) | ' gaiking space robot ' |

Upper( string ) | ' GAIKING SPACE ROBOT ' |

Keep in mind that you can use string functions on other string functions that return strings. In other words, this is a perfectly legal set of calls that returns the length of a left and right trimmed string: ` Len( LTrim( RTrim( string ) ) ) ` |

In addition to the string functions, there are several invaluable date manipulation functions as well.

The ` DateAdd( ` ` datepart ` ` , ` ` number ` ` , ` ` date ` ` ) ` function can be used to add a chosen unit of time to a particular date. The first argument, ` datepart ` , controls the part of the date you are adding. For a complete list of values for the ` datepart ` argument, see Table 3.2. ` Number ` is the amount of the chosen ` datepart ` you're adding to the date. For instance, in order to add two months to the current date, you can use the following:

DateAdd( m, 2, getdate() )

Code | Symbol |
---|---|

Year | yy, yyyy |

Quarter | qq, q |

Month | mm, m |

Dayofyear | dy, y |

Day | dd, d |

Week | wk, ww |

Hour | hh |

Minute | mi, n |

Second | ss, s |

Millisecond | ms |

The functions ` Month() ` , ` Day() ` , and ` Year() ` are used to return the corresponding piece of a given date. For instance, ` Month('12/7/1952') ` returns 12, ` Day('12/7/1952') ` returns 7, and ` Year('12/7/1952') ` returns 1952. These functions can save hours of needless parsing of dates by hand.

One last function that is indispensable when working with dates is ` Datediff ( ` ` datepart ` ` , ` ` startdate ` ` , ` ` enddate ` ` ) ` . This function returns the difference of two dates in units determined by the ` datepart ` argument. Fortunately, it also uses the codes shown in Table 3.2 for the values in its first argument.

SQL Server contains a number of methods for working with numbers . You probably will never use most of them in a query (when was the last time you needed to compute the arctangent of a value as part of a query?). However, when you do need one of these methods, they are quite handy. Table 3.3 shows some math functions and their return values. For a complete list, please see Microsoft SQL Server Books Online.

Function | Description |
---|---|

Abs( expr ) | Returns the absolute positive value. |

Cos( expr ) | Returns the cosine. |

Exp( expr ) | Returns exponential value. |

Log( expr ) | Returns natural logarithm. |

Pi() | Returns the value of Pi. |

Rand( [seed] ) | Returns a random number. The seed is an optional argument giving Rand() a start value. |

Sin( expr ) | Returns the sine. |

Square( expr ) | Returns the square. |

Sqrt( expr ) | Returns the square root. |

Tan( expr ) | Returns the tangent. |

for RuBoard |

Sams Teach Yourself ADO.NET in 24 Hours

ISBN: 0672323834

EAN: 2147483647

EAN: 2147483647

Year: 2002

Pages: 237

Pages: 237

Authors: Jason Lefebvre, Paul T. Bertucci

- ERP Systems Impact on Organizations
- Challenging the Unpredictable: Changeable Order Management Systems
- ERP System Acquisition: A Process Model and Results From an Austrian Survey
- The Effects of an Enterprise Resource Planning System (ERP) Implementation on Job Characteristics – A Study using the Hackman and Oldham Job Characteristics Model
- Healthcare Information: From Administrative to Practice Databases

flylib.com © 2008-2017.

If you may any questions please contact us: flylib@qtcs.net

If you may any questions please contact us: flylib@qtcs.net