Đang chuẩn bị liên kết để tải về tài liệu:
Joe Celko s SQL for Smarties - Advanced SQL Programming P21

Joe Celko s SQL for Smarties - Advanced SQL Programming P21. In the SQL database community, Joe Celko is a well-known columnist and purveyor of valuable insights. In Joe Celko's SQL for Smarties: Advanced SQL Programming, he picks up where basic SQL training and experience leaves many database professionals and offers tips, techniques, and explanations that help readers extend their capabilities to top-tier SQL programming. Although Celko denies that the book is about database theory, he nevertheless alludes to theory often to buttress his practical points. This title is not for novices, as the author points out. Instead, its intended audience. | 172 CHAPTER 5 CHARACTER DATA TYPES IN SQL 5.1.3 Problems of String Grouping Because the equality test has to pad out the shorter of the two strings you will often find doing a group by on a VARCHAR n has unpredictable results CREATE TABLE Foobar x VARCHAR 5 NOT NULL INSERT INTO Foobar VALUES a a a a Now execute the query SELECT x CHAR_LENGTH x FROM Foobar GROUP BY x The value for CHAR_LENGTH x will vary for different products. The most common answers are 1 4 or 5 in this example. A length of 1 is returned because it is the length of the shortest string or because it is the length of the first string physically in the table. A length of 4 is returned because it is the length of the longest string in the table and a length of 5 because it is the greatest possible length of a string in the table. You might want to add a constraint that makes sure to trim the trailing blanks to avoid problems. 5.2 Standard String Functions SQL-92 defines a set of string functions that appear in most products but with vendor-specific syntax. You will probably find that products will continue to support their own syntax but will also add the Standard SQL syntax in new releases. String concatenation is shown with the operator taken from PL I. The SUBSTRING string FROM start FOR length function uses three arguments the source string the starting position of the substring and the length of the substring to be extracted. Truncation occurs when the implied starting and ending positions are not both within the given string. DB2 and other products have a left and a right function. The LEFT function returns a string consisting of the specified number of leftmost characters of the string expression and the right well that is kind of obvious. O 5.2 Standard String Functions 173 The fold functions are a pair of functions for converting all the lowercase characters in a given string to uppercase UPPER string or all the uppercase ones to lowercase LOWER string . TRIM trim specification trim character