System function:
SELECT HOST_ID () Host_ID, Convert (Char (13), Host_Name ()) Host_Name, SUSER_ID () 'SUSER_ID ()', User_ID () 'User_ID', Convert (CHAR (3), User_name (1)) User_name, Convert (CHAR (3), USER) 'User', DB_ID ('Master') 'DB_ID (' 'Master' ')', Convert (CHAR (9), DB_NAME (2)) 'DB_NAME (2)', Datalength ( 'Aaaaaa') 'Datalength (' 'aaaaaaa') ', Datalength (3245)' Datalength (3245) ', DATALENGTH (Getdate ())' DATALENGTH (Getdate ()) '
result:
HOST_ID HOST_NAME SUSER_ID () User_id user_name user db_id ('master') db_name (2)
3960 WWW-Eleaf-Org Null 1 DBO DBO 1 TEMPDB
Datalength ('Aaaaaaa') Datalength (3245) Datalength (Getdate ()) 6 4 8
(The number of rows affects is 1 line)
Date function datename ():
Declare @var DateTime; select @var = getdate (); select datename (yy, @ var) year, datename (mm, @ var) month, datename (dd, @ var) day, datename (hh, @var) HOUR, Datename (mi, @var) minute, datename (s, @var) second, datename (ms, @var) Millisecond, Datename (@var) quarter, datename (dy, @var) dayofyear, datename (wk, @ Var) Week, Datename (DW, @var) weekday;
result:
Year Month Day Hour Minute Second Millisecond Quarter Dayofyear Week Weekday
2005 03 8 18 6 38 217 1 67 11 Tuesday
(The number of rows affects is 1 line)
Date function datepart ():
Declare @var datetime; select @var = getdate (); select datepart (yy, @ var) year, datepart (mm, @ var) month, datepart (dd, @ var) day, datepart (hh, @var) HOUR, DatePart (MI, @var) minute, datepart (s, @var) second, datepart (ms, @var) Millisecond, Datepart (@var) quarter, datepart (dy, @var) dayofyear, datepart (wk, @ VAR) Week, Datepart (DW, @var) Weekday; Result:
Year Month Day Hour Minute Second MilliseCond Quarter Dayofyear Week Weekday 2005 3 8 18 11 38 250 1 67 11 3
(The number of rows affects is 1 line)
Date function dateadd ():
Select Dateadd (YY, 30, getDate ()) '30 years later ', DateAdd (mm, 30, getdate ())' 30 months later ';
result:
30 months after 30 months later 2035-03-08 18: 18: 04.687 2007-09-08 18: 18: 04.687
(The number of rows affects is 1 line)
Conversion function control ():
Declare @var DateTimeSelect @var = getdate (); select convert (int, @var) INT, Convert (VARCHAR (20), @var) VARCHAR, Convert (Money, @var) Money, Convert (Real, @var) REAL , Convert, @var) Numeric, Convert (binary, @var) Binary, Convert (TimeStamp, @var) TimeStamp, Convert (Decimal, @var) Decimal, Convert (Bit, @var) bit;
result:
INT VARCHAR MONEY REAL FLOAT NUMERIC 38418 03 8 2005 6:39 PM 38417.7777 38417.777 38417.777684259258 38418
binary
0x0000000000000000000000000000000000000000000000000000000000000000000000000000961101339488
TimeStamp Decimal Bit
0x0000961101339488 38418 1
(The number of rows affects is 1 line)
Date format conversion with the control () function:
Declare @var DateTime; select @var = getdate (); select convert (char (11), @ var, 107) '107', Convert (char (9), @var, 7) '7', Convert (char (char (char) 8), @ var, 108) '108', Convert (Char (23), @var, 121) '121', Convert (Char (19), @var, 120) '120', Convert (Char (19) @var, 20) '20'; results:
107 7 108 121 120 20 ------------------------------ ------------------------------------------- 03 09, 2005 03 09, 05 00:41:06 2005-03-09 00: 41: 06.310 2005-03-09 00:41:06 2005-03-09 00:41:06
(The number of rows affects is 1 line)
ISNULL () function:
Declare @var int; select @var = null; select isnull (@var, 123); // Because @var is null value, the second parameter is returned. SELECT @var = 456; select isnull (@var, 123); // Because @var is not empty, return it itself.
result:
----------- 123
(The number of rows affects is 1 line)
----------- 456
(The number of rows affects is 1 line)
Nullif () function:
Declare @var int; select @var = 15; select nullif (@var, 15); // Because the two parameter values are the same, the null value is returned. Select Nullif (@var, 16); // Because the two parameter values are different, the first parameter itself is returned.
result:
----------- NULL
(The number of rows affects is 1 line)
----------- 15
(The number of rows affects is 1 line)
Coalesce () Function: Returns the first value in the list. (Can be used to filter null values)
Declare @a int, @B int, @c @B = null, @c = 13; select coalesce (@a, @B, @c); select coalesce (null, 15, null);
result:
----------- 13
(The number of rows affects is 1 line)
----------- 15
(The number of rows affects is 1 line)
String function:
Declare @A CHAR (15); select @a = 'bitanstudio2004'; Select Datanceth (@a) Datalength, Substring (@A, 1, 5) Substring, Convert (CHAR (4), (Right (@a, 4) )) 'Right', CONVERT (CHAR (15), Upper (@a)) 'Upper', 'a' Space (3) 'B' Space, Replicate ('AB', 3) Replicate; SELECT Convert CONVERT CHAR (12), Stuff (@a, 1, 5, '**)) stuff, convert (char (15), Reverse (@a)) Reverse, Convert (Char (2), Ltrim (' ab ') LTRIM, CONVERT (CHAR (2), ASCII ('Abcd')) ASCII, Convert (Char (1), Char (67)) CHAR, Convert (CHAR (4), STR (135.0)) STR;
result:
DatabilityEngth Substring Right Upper Space Replicate --------------------------------------- --- - --------- 15 Bitan 2004 BitanStudio2004 a b Ababab
(The number of rows affects is 1 line)
Stuff Reverse Ltrim Ascii Char Str ------------ ----------------------- ---- --- - ** Studio2004 4002OIDUTSNATIB AB 97 C
(The number of rows affects is 1 line)
Soundex () function:
Select Soundex ('Bitan') Bitan, Soundex ('Biitaan') Biitaan, Soundex ('BiQaname') bicaname, Soundex ('biqaname') biqaname;
result:
Bitan Biitaan Bicaname Biqaname ------------------ -------- B526 B526 B526 B562
(The number of rows affects is 1 line)
DIFFERENCE () function:
Select Difference ('Bitan', 'Biqan') 'Bitan-Biqan', Difference ('Bitan', 'BEetane') 'Bitan-Beetane', Difference ('Bitan', 'Apple') 'Bitan-Apple', Difference ('Bitan', 'Biitaan') 'Bitan-Biitaan';
result:
Bitan-biqan Bitan-Beetane Bitan-Apple Bitan-Biitaan ----------- ------------- ----------- --- ---------- 3 3 0 4 (the number of rows affects is 1 line)
CHARINDEX () function:
Declare @var char (20), @ var2 char (5); select @var = 'bbaaaaabbaaaaaaab', @ var2 = null; select charIndex ('aaa', @var, 2), charIndex ('bb', @var, 3), Charindex ('BAA', @var), Charindex (@ var2, @var, 3);
result:
----------- ------------------------------- 3 8 2 NULL
(The number of rows affects is 1 line)
Patindex () function: (using regular expressions)
Declare @var char (30); select @var = 'bitanstudionowisopen'; select Patindex ('% Tan%', @var) 'Tan', Patindex ('% _ s%', @var) '_s', Patindex (' % T [Ag] n% ', @var)' t [Ag] n ', Patindex ('% o [^ AG] E% ', @var)' o [^ AG] E ', Patindex ('% T [aeiou] n% ', @var)' t [aeiou] n ', Patindex ('% T% N% ', @var)' T% n ';
result:
Tan _S T [AG] N o [^ AG] e t [aeiou] N T% N --------------------------- ---- ------------------------------- 3 5 3 17 3 3
(The number of rows affects is 1 line)
Mathematical functions:
SELECT RTRIM (Power (2,5)) 'Power (2, 5)', Convert (CHAR (5), (Power (2.00, 5))) 'Power (2.00, 5)', ABS (-1) ' ABS (-1) ', CEILING (2.5)' CEILING (2.5), Convert (Char (6), DegRees (2 * pi ())) 'DegRees (2 * pi ())', Floor (3.5) ' Floor (3.5) '; select convert (int, rand () * 10)' Rand () * 10 ', Convert (int, Rand (5) * 10)' Rand (5) * 10 '; SELECT ROUND (Pi ), 0) 'ROUND (Pi (), 0)'; SELECT ROUND (Pi (), 5) '; SELECT ROUND (Pi (), 1)' Round (Pi () , 1) ', SIGN (-59)' siGN (-59) ', SQRT (1.44)' SQRT (1.44) '; SELECT SQRT (-52)' SQRT (-52) ';
Power (2,5) Power (2.00, 5) ABS (-1) CEILING (2.5) DegRees (2 * pi ()) Floor (3.5) --------------- -------- ------------------------------------- -------- 32 32.00 1 3 360 3
(The number of rows affects is 1 line)
Rand () * 10 rand (5) * 10 ---------------------- 4 7
(The number of rows affects is 1 line)
Round (pi (), 0) --------------------- 3.0
(The number of rows affects is 1 line)
Round (Pi (), 5) --------------------- 3.141589999999999
(The number of rows affects is 1 line)
Round (pi (), 1) sign (-59) SQRT (1.44) --------------------------------- -------------------- ----------------------- 3.10000000000000000001 -1 1.2
(The number of rows affects is 1 line)
A domain error. SQRT (-52) --------------------------------------------- ------------
