226 Part I EXAM PREPARATION Where: Expression
226 Part I EXAM PREPARATION Where: Expression is a character string, binary string, text, image, a column, or an expression that includes a column. Start is a number denoting the initial position of the sub-string. Length is a number denoting how long the sub-string is. WARNING This example shows how SUBSTRING works: Avoid Using SUBSTRING The SUB SELECT Au_fname + + au_lname AS full name , STRING function, depending on how SUBSTRING (au_fname, 1,1) + SUBSTRING (au_lname, 1,1) AS it is used,as in a WHERE clause, . initials FROM authors may perform a table scan where an index was supposed to function The next section looks at using DATALENGTH to count the number of (if an index was implemented). bytes used to represent an expression. When the SUBSTRING function does not include the first letter of the column being searched, a DATALENGTH table scan is performed. You may need to know how many bytes long a string is. Of course, you could count the number of characters present in a string, but that would be a complete waste of time. You might also have problems differentiating standard one-byte strings and Unicode two-byte strings. For example, if you were creating an application with a first name column of a fixed length, you would need to observe previous tables with a first name column to get an estimate of the highest first name present to set the fixed length. An easy way to do this would be to use a function known as DATALENGTH in conjunction with MAX; in this way, you would certainly save time and get results. The DATALENGTH function returns the number of bytes used in an expression supplied. The syntax for DATALENGTH is DATALENGTH (expression) Where: Expression is the data you want to find the length of. Filtering and formatting data can be carried further with elements of grouping and computing results. These features are defined further in Chapter 5, Advanced Data Retrieval and Modification. The final area of SQL Server functionality left to discuss is a series of functions that allow for a variety of system-level interactions.
For reliable and cheap web hosting services please check javaweb hosting website.