5 S Q L SQL Server 5.1 5-1 SQL Server 5-1 A B S A C O S A S I N ATA N AT N 2 C E I L I N G C O S C O T D E G R E E S E X P F L O O R L O G L O G 10 P I P O W E R R A D I A N S R A N D R O U N D S I G N S I N S Q RT S Q U A R E TA N 1 1 0 π 1 1 π/ 2 π/ 2 + π / 2 π/ 2 ( y / x ) + π / π e n e = 2. 71828183 i n p u t _ n u m b e r 0 i n p u t _ b a s e i n p u t _ n u m b e r 1 i n p u t _ n u m b e r 0 π = 3. 141592653589793 ( i n p u t _ x ) ( i n p u t _ y ) i n p u t _ x i n p u t _ y i n p u t _ x i n p u t _ y 0 1 i n p u t _ x 0 0 1 0 0 0 1 i n p u t _ n u m b e r i n p u t _ n u m b e r
80 A B S C E I L I N G F L O O R P O W E R R A N D R O U N D S Q RT 5.1.1 ABS A B S A B A B S (i n p u t _ n u m b e r) E D I A B S 1. A B S A B S ( E O Q ) E O Q = S Q RT ( 2 * A b s ( S ) * A b s ( O ) / A b s ( C ) ) S O C ( ) A B S S 2 000 O $ 8. 00 C $ 0. 20 SELECT SQRT(2.0*ABS (2000.0) * ABS ( 8.0) / ABS (0.20)) - - - - - - - - - - - - - - - - - - 4 0 0. 0 S Q RT E D I 2. A B S $ 0. 00 ABS(loss reserve) ABS(expense reserve)
5 81 5.1.2 CEILING C E I L I N G i n p u t _ n u m b e r C E I L I N G (i n p u t _ n u m b e r) 2. 35 1. 3 C E I L I N G A B S ( E O Q ) C E I L I N G C E I L I N E O Q SELECT CEILING(SQRT (2.0*ABS (1985.0) * ABS ( 8.0) / ABS (0.20))) - - - - - - - - - - - - - - - - - - 3 9 9. 0 R O U N D 5.1.3 FLOOR F L O O R i n p u t _ n u m b e r F L O O R (i n p u t _ n u m b e r ) F L O O R 1. F L O O R SQL Server M O D F L O O R SQL Server x-y*floor (x/y) 5 2 1 SELECT 5-2 * FLOOR (5/2) - - - - - - - - - - - - - - - - - - - - - - - 1 T- S Q L 5 Mod 2 F L O O R 2. F L O O R M i c r o s o f t T R U N C F L O O R F L O O R X-FLOOR (x) SELECT 1.22-FLOOR (1.22) - - - - - - - - - - - - - - - - - - - - - - - 0. 2 2
82 SQL Server π P I F L O O R SELECT PI() F L O O R ( P I ( ) ) 5.1.4 POWER P O W E R ( i n p u t _ x ) ( i n p u t _ y ) i n p u t _ x i n p u t _ y i n p u t _ x i n p u t _ y P O W E R P O W E R F V *( 1 /( 1 + i ) n ) F V i n SQL Server 1464 10 4 SELECT 1464 * (1/POWER ((1+.10),4)) - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 1 0 0 2. 7 3 9 7 2 6 0 2 7 3 9 7 2 6 0 2 7 3 9 7 2 6 0 2 7 3 9 7 2 6 R O U N D 5.1.5 RAND R A N D SQL Server O r a c l e D a t a e a s e I n f o r m i x Q u e s t i o n N u m b e r Q u e s t i o n N u m b e r 1 n ( n ) R A N D 100 I D 5-1 Visual Basic Visual C++ C R E ATE TABLE Questions ( ) G O intquestionid INT, v c h Q u e s t i o n Text VA R C H A R ( 5 1 2 ) 5-1 R A N D C R E ATE TABLE Te s t Q u e s t i o n s (
5 83 ) G O intquestionid INT, v c h Q u e s t i o n Text VA R C H A R ( 5 1 2 ) /* This section populates the questions table */ DECLARE @intcounter INT DECLARE @vchquestion VA R C H A R ( 64 ) SET NOCOUNT ON SELECT @intcounter = 1 WHILE (@intcounter <= 100) B E G I N SELECT @vchquestion = Test Question #' + C O N V E RT ( VARCHAR(8), @intcounter) I N S E RT INTO Questions VALUES (@intcounter, @vchquestion) SELECT @intcounter = @intcounter + 1 E N D G O /* This section generates the random questions */ DECLARE @intquestionid INT SET NOCOUNT ON DELETE FROM Te s t Q u e s t i o n s SELECT @intquestionid = CEILING(RAND()*100) WHILE (SELECT COUNT(*) FROM TestQuestions) < 100 B E G I N E N D B E G I N E N D 5.1.6 ROUND WHILE (SELECT COUNT(*) FROM TestQuestions WHERE intquestionid = @intquestionid) = 1 SELECT @intquestionid = CEILING(RAND()*100) I N S E RT INTO Te s t Q u e s t i o n s SELECT * FROM Questions WHERE intquestionid = @intquestionid R O U N D i n p u t _ x 0 R O U N D
84 ROUND (i n p u t _ x, < p l a c e s _ t o _ r o u n d >) P O W E R P O W E R 32 C O N V E RT S Q L SELECT CONVERT (money, 1002.739726027397260273972602739726) T- S Q L R O U N D R O U N D SELECT ROUND (1464 * (1/POWER (1+.10), 4)), 2) - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 1 0 0 2. 7 4 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 R O U N D 0 32 ( ) 3 32 ( ) 5.1.7 SQUARE S Q U A R E i n p u t _ n u m b e r SQUARE (i n p u t _ n u m b e r) 5.1.8 SQRT S Q RT S Q RT (i n p u t _ n u m b e r) i n p u t _ n u m b e r S Q RT 5. 1. 1 A B S A B S E O Q 5.2 SQL Server C H A R ( VA R C H A R ) D AT E T I M E ( S M A L L D AT E T I M E ) 5-2
5 85 5-2 Y Y 1 7 5 3 9 9 9 9 Q Q 1 4 M M 1 1 2 D Y 1 3 6 6 D D 1 3 1 W K 1 5 3 D W 1 7 S u n S a t H H 0 2 3 ( ) M I 0 5 9 S S 0 5 9 M S 0 9 9 9 SQL Server 50 50 01 / 01 / 70 1970 01 / 01 / 36 2036 1940 1970 30 40 7 0 SQL Server 2040 1 970 70 10 SQL Server SQL Enterprise M a n a g e r s p _ c o n f i g u r e 5-3 SQL Server 5-3 D AT E A D D D AT E D I F F D AT E N A M E D AT E PA RT G E T D AT E D AY M O N T H Y E A R d a t e p a r t d a t e p a r t d a t e p a r t d a t e p a r t d a t e p a r t d a t e p a r t 5.2.1 DATEADD D ATEADD (datepart, number, date) d a t e p a r t 5-2 S E L E C T 30 SELECT DAREADD (DY, 30, '10/01/98')
86 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - Oct 31 1998 12:00AM SQL Server 5.2.2 DATEDIFF D AT E D I F F d a t e p a r t D ATEDIFF (datepart, date1, date2) S E L E C T SELECT DATEDIFF (DY, '08/30/98', '10/01/98') - - - - - - - - - - - - - - - - - - - - - 3 2 D u e D a t e C u r r e n t D a t e ( ) 10 2 % I N T E G E R I N T E G E R I N T E G E R 1973 9 1 1975 9 1 630 ( 100 m s 6 0 s e c 6 0 m i n 3 65 d y 2 y r ) I N T E G E R 5.2.3 DATENAME D ATENAME (datepart, date) SELECT DATENAME (DW, '10/01/98') - - - - - - - - - - - - - - - - - - - - - W E D N E S D AY 8 1 2 date part ( D W ) ( M M ) 5.2.4 DATEPART d a t e p a r t ( D AT E PA RT d a t e p a r t )
5 87 D AT E PA RT D AT E PA RT (datepart, date) S E L E C T SELECT DAT E PA RT (MM, 'Oct 31 1998') - - - - - - - - - - - - - - - - - - - - - 1 0 I N T E G E R T- S Q L W H E R E 5.2.5 GETDATE G E T D AT E SELECT GETDATE () - - - - - - - - - - - - - - - - - - - - - - - - Aug 12 1998 5:30PM G E T D AT E Report Generated By: Report Generated On: G e t D a t e 5.2.6 DAY D AY D AT E PA RT(dd, date) 5.2.7 MONTH M O N T H D AT E PA RT(mm, date) 5.2.8 YEAR Y E A R DATEPART (yy, date) 5.3 SQL Server
88 5-4 5-4 + A S C I I C H A R C H A R I N D E X D I F F E R E N C E L E F T L E N L O W E R LT R I M N C H A R PAT I N D E X R E P L A C E Q U O T E N A M E R E P L I C AT E R E V E R S E R I G H T RT R I M S O U N D E X S PA C E S T R S T U F F S U B S T R I N G U N I C O D E U P P E R A S C I I A S C I I I N T E G E R A S C I I 0 2 55 N U L L 0 4 4 U n i c o d e U n i c o d e C H A R I N D E X PAT I N D E X U n i c o d e S Q L S e r v e r (') (") ([ ]) S O U N D E X U n i c o d e 5-4 C H A R I N D E X D I F F E R E N C E L O W E R LT R I M RT R I M R E V E R S E S T R S U B S T R I N G U P P E R 5.3.1 CHARINDEX CHARINDEX (pattern, string_value) ( 0 )
5 89 5.3.2 DIFFERENCE 0 4 S O U N D E X 0 4 D I F F E R E N C E DIFFERENCE (string1, string2) Kathy Wa r d C a t h y K a t h y K a t h i e 3 S O U N D E X Colleen Hogan 100 Colleen Hogan D I F F E R E N C E 5.3.3 LOWER L O W E R LOWER (i n p u t _ s t r i n g) 5.3.4 LTRIM LTRIM ( i n p u t _ s t r i n g) E D I RT R I M 5.3.5 REVERSE REVERSE (i n p u t _ s t r i n g) R E V E R S E S Q L P a s s w o r d 5.3.6 RTRIM RTRIM ( i n p u t _ s t r i n g) E D I S E L E C T C H A R C H A R
90 5.3.7 STR STR ( i n p u t _ n u m b e r, total_length, decimal) i n p u t _ n u m b e r t o t a l _ l e n g t h d e c i m a l ( 10 ) 5.3.8 SUBSTRING S U B S T R I N G SUBSTRING (input_string, starting_position, length) i n p u t _ s t r i n g s t a r t i n g _ p o s i t i o n i n p u t _ s t r i n l e n g t h i n p u t _ s t r i n g s t a r t i n g _ p o s i t i o n S U B S T R I N G phone number n a m e (last name) U P C 5.3.9 SQL Server S E L E C T 1. First Name L a s t N a m e Last Name First Name 5-2 T- S Q L DECLARE @chvfullname VA R C H A R ( 3 0 ), @chvreorderedname VA R C H A R ( 3 0 ) SELECT @chvfullname = Julie Ely' SELECT @chvreorderedname = SUBSTRING(@chvFullName, -- create Last Name (CHARINDEX(" ",@chvfullname) + 1 ), 30) + ', ' + -- Comma and Space Separator 5-2 SUBSTRING(@chvFullName, -- Create First Name
5 91 1, (CHARINDEX(" ",@chvfullname) - 1 )) PRINT @chvreorderedname Your output should look similar to the following: - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - E l y, Julie S U B S T R I N G ( l a s t _ n a m e ) C H A R I N D E X 1 S U B S T R I N G 1 ( 5-2 ) C H A R I N D E X 1 (first name) (last name) char (30) SQL Server LT R I M R E V E R S E D ATA L E N G T H 2. (Sentence Case) SQL Server SELECT UPPER (SUBSTRING ("this Is a test.",1,1)) + LOWER (SUBSTRING ("this Is a test." 2, 20)) - - - - - - - - - - - - - - - - - - - - - - This is a test. S U B S T R I N G U P P E R S U B S T R I N G L O W E R
92 M r. D r. You've just won $9999.99 3. SQL Server T R U N C AT E 5. 1. 3 F L O O R SELECT SUBSTRING (STR (4.1313, 20, 6), CHARINDEX (".", STR (4.1313, 20, 6)), 7) - - - - - - - - - - - - - - - - - - - - - - 0. 1 3 1 3 0 0 C O N V E RT S U B S T R I N G S T R C H A R I N D E X 6 S U B S T R I N G 7 1 6 SQL Server 4. D I F F E R E N C E Kathy Wa r d SELECT DIFFERENCE("Kathy Ward","kathy WA R D " ), D I F F E R E N C E ( " K ATHY Ward","Cathy Wa r d " ), D I F F E R E N C E ( " K ATHY Ward","Cathie Wa r d " ) ------ --------- ------- 4 3 3 D I F F E R E N C E 4 3 S E L E C T 3 5.4 SQL SQL Server T E X T I M A G E 5-5 T E X T I M A G E
5 93 5-5 TEXT I M A G E T E X T P T R T E X T VA L I D T E X T P T R PAT I N D E X D ATA L E N G T H 5.5 SQL SQL Server N U L L I F C O A L E S C E S Q L 5-6 5-6 A P P _ N A M E C O A L E S C E C O L _ L E N G T H C U R R E N T _ T I M E S TA M P C U R R E N T _ U S E R D ATA L E N G T H D B _ I D D B _ N A M E G E TA N S I N U L L H O S T _ I D H O S T _ N A M E I D E N T _ I N C R I D E N T _ S E E D I N D E X _ C O L I S D AT E I S N U L L I S N U M E R I C N E W I D N U L L I F O B J E C T _ I D O B J E C T _ N A M E PA R S E _ N A M E P E R M I S S I O N S S E S S I O N _ U S E R S TAT S _ D AT E N U L L C A S E N U L L T R U E FA L S E G E T D AT E () U S E R _ N A M E C H A R A C T E R B I N A RY T E X T I M A G E ANSI NULL 1 I D i d e n t i t y S E E D ( ) N U L L u n i q u e i d e n t i f i e r e x p r e s s i o n 1 e x p r e s s i o n 2 N U L L C A S E I D
94 S Y S T E M _ U S E R S U S E R _ I D U S E R _ N A M E U S E R _ S I D U S E R _ I D U S E R I D I D I D D ATA L E N G T H I S N U L L H O S T _ N A M E S U S E R _ N A M E U S E R 5.5.1 DATALENGTH D ATA L E N G T H N U L L N U L L C H A R N U L L I S N U L L N U L L ( ) I S N U L L D ATA L E N G T H D ATA L E N G T H (i n p u t _ d a t a) i n p u t _ d a t a C H A R A C T E R B I N A RY T E X T I M A G E M i c r o s o f t D ATA L E N G H RT R I M 1. D ATA L E N G T H D ATA L E N G T H SELECT UPPER(SUBSTRING("this Is a test.",1,1)) + LOWER(SUBSTRING("this Is a test.",2, ( D ATALENGTH("this Is a test.')-1))) - - - - - - - - - - - - - - - - - - This is a test. 20 3 1
5 95 2. D ATA L E N G T H C O L _ L E N G T H D ATA L E N G T H C O L _ L E N G T H t i t l e s SELECT title,datalength(title) "Actual", C O L _ L E N G T H ( t i t l e s ', ' t i t l e ' ) " D e f i n e d " FROM titles ORDER BY DATA L E N G T H ( t i t l e ) i t l e A c t u a l Defined ----------------------------------------------------- ------ ------- ------ ------- ------ ------- ------ -- Net Etiquette 1 3 8 0 Sushi, Anyone? 1 4 8 0 Life Without Fear 1 7 8 0 Is Anger the Enemy? 1 9 8 0 The Gourmet Microwave 2 1 8 0 But Is It User Friendly? 2 4 8 0 Secrets of Silicon Va l l e y 2 5 8 0 Straight Talk About Computers 2 9 8 0 You Can Combat Computer Stress! 3 1 8 0 Silicon Valley Gastronomic Tr e a t s 3 3 8 0 The Psychology of Computer Cooking 3 4 8 0 Emotional Security: A New Algorithm 3 5 8 0 The Busy Executive's Database Guide 3 5 8 0 Fifty Years in Buckingham Palace Kitchens 4 1 8 0 Prolonged Data Deprivation: Four Case Studies 4 5 8 0 Cooking with Computers: Surreptitious Balance Sheets 5 2 8 0 1 A c t u a l _ Wi d t h D ATA L E N G T H C O L _ L E N G T H 40 4 ORDER BY D ATA L E N G T H 5.5.2 ISNULL N U L L ISNULL (expression, replacement_value) e x p r e s s i o n r e p l a c e m e n t _ v a l u e N U L L N U L L N U L L I S N U L L S t a n d a r d C o s t N U L L 0 N U L L
96 5.5.3 HOST_NAME HOST_NAME () SQL Server H O S T _ N A M E SQL Query Analyzer SQL Server H O S T _ N A M E SQL Server H O S T _ N A M E 5.5.4 SUSER _NAME SUSER_NAME( ) 5.5.5 USER USER () S U S E R _ N A M E D B O U S E R D B O S A U S E R D B O S U S E R _ N A M E S A 5.6 SQL Server C O N V E RT