-SQL Server Functions

--------- Mathematical functions

1. Absolute Value
S: Select ABS (-1) Value
O: Select ABS (-1) value from dual

2. INTEGER (large)
S: Select ceiling (-1.001) Value
O: Select Ceil (-1.001) value from dual

3. Round (small)
S: Select floor (-1.001) Value
O: Select floor (-1.001) value from dual

4. Round (truncation)
S: Select cast (-1.002 as INT) Value
O: Select trunc (-1.002) value from dual

5. Rounding
S: Select round (1.23456, 4) value 1.23460
O: Select round (1.23456, 4) value from dual 1.2346

6. e is the base power
S: Select exp (1) Value 2.7182818284590451
O: Select exp (1) value from dual 2.71828182

7. Take the base logarithm of E.
S: Select log (1, 2.7182818284590451) value 1
O: Select Ln (2.7182818284590451) value from dual; 1

8. Use 10 as the base logarithm.
S: Select log10 (10) value 1
O: Select log (10, 10) value from dual; 1

9. Square
S: Select square (4) value 16
O: Select power (4, 2) value from dual 16

10. Take the square root
S: Select SQRT (4) value 2
O: Select SQRT (4) value from dual 2

11. Evaluate the base power of any number
S: Select power (3, 4) value 81
O: Select power (3, 4) value from dual 81

12. Random Number acquisition
S: Select rand () Value
O: select Sys. dbms_random.value (0, 1) value from dual;

13. Get the symbol
S: Select sign (-8) Value-1
O: Select sign (-8) value from dual-1

---------- Mathematical functions

14. Circumference Rate
S: Select Pi () value 3.1415926535897931
O: Unknown

15. Sin, cos, and Tan parameters are in radians.
For example, select sin (PI ()/2) value to get 1 (sqlserver)

16. asin, ACOs, atan, atan2 return radians

17. radian angle Interchange (, Oracle unknown)
Degrees: Radian-> Angle
Radians: Angle-> radian

--------- Comparison of Values

18. Calculate the maximum value of the Set
S: Select max (value) value from
(Select 1 Value
Union
Select-2 value
Union
Select 4 value
Union
Select 3 value)

O: Select greatest (1,-2, 4, 3) value from dual

19. Minimum value of the Set
S: select Min (value) value from
(Select 1 Value
Union
Select-2 value
Union
Select 4 value
Union
Select 3 value)

O: Select least (1,-2, 4, 3) value from dual

20. How to Handle null values (replace null with 10 in F2)
S: Select F1, isnull (F2, 10) value from TBL
O: Select F1, nvl (F2, 10) value from TBL

-------- Comparison of Values

21. Search for the character serial number
S: Select ASCII ('A') Value
O: Select ASCII ('A') value from dual

22. calculate characters from the serial number
S: Select char (97) Value
O: Select CHR (97) value from dual

23. Connection
S: Select '11' + '22' + '33' Value
O: Select Concat ('11', '22') | 33 value from dual

23. substring position -- return 3
S: Select charindex ('s ', 'sdsq', 2) Value
O: Select instr ('sdsq', 's', 2) value from dual

23. Position of the fuzzy substring -- 2 is returned. If % is removed from the parameter, 7 is returned.
S: Select patindex ('% d % Q %', 'sdsfasdqe ') Value
O: no oracle found, but instr can use the fourth parameter to control the number of occurrences.
Select instr ('sdsfasdqe ', 'sd', 1, 2) value from dual returns 6

24. Seek substrings
S: Select substring ('abcd', 2, 2) Value
O: Select substr ('abc', 2, 2) value from dual

25. Return aijklmnef as a substring
S: select stuff ('abcdef', 2, 3, 'ijklmn ') Value
O: Select Replace ('abcdef', 'bcd', 'ijklmn ') value from dual

26. replace all substrings
S: No
O: Select translate ('fasdbfasegas', 'fa ', 'my') value from dual

27. Length
S: Len, datalength
O: Length

28. case-insensitive lower, upper

29. uppercase letters
S: No
O: Select initcap ('abcd DSAF df') value from dual

30. Left fill space (the first parameter of lpad is space, which is the same as the space function)
S: Select space (10) + 'abc' Value
O: Select lpad ('abc', 14) value from dual

31. Right fill space (the first parameter of rpad is space, which is the same as the space function)
S: Select 'abc' + space (10) Value
O: Select rpad ('abcd', 14) value from dual

32. Delete Spaces
S: ltrim, rtrim
O: ltrim, rtrim, trim

33. Duplicate string
S: Select replicate ('abcd', 2) Value
O: No

34. Comparison of pronunciation similarity (the two words return the same value and have the same pronunciation)
S: Select soundex ('Smith '), soundex ('smy ')
O: Select soundex ('Smith '), soundex ('smy') from dual
Comparison of soundex with select difference ('smithers', 'smythers ') in sqlserver
Returns 0-4, 4 for homophone, 1 for maximum

-------------- Date functions

35. system time
S: Select getdate () Value
O: Select sysdate value from dual

36. days before and after
Directly add or subtract from an integer

37. Calculate the date
S: Select convert (char (10), getdate (), 20) Value
O: Select trunc (sysdate) value from dual
Select to_char (sysdate, 'yyyy-mm-dd') value from dual

38. Time
S: Select convert (char (8), getdate (), 108) Value
O: Select to_char (sysdate, 'hh24: mm: ss') value from dual

39. Take other parts of date and time
S: datepart and datename functions (determined by the first parameter)
O: The second parameter of the to_char function is determined.

Parameter --------------------------------- The following table needs to be supplemented.
Year YY, yyyy
Quarter QQ, Q (quarter)
Month mm, M (m o is invalid)
Dayofyear dy, y (O table week)
Day DD, D (d o is invalid)
Week wk, ww (WK o is invalid)
Weekday DW (unclear O)
Hour HH, hh12, hh24 (hh12, hh24 S is invalid)
Minute Mi, n (n o is invalid)
Second SS, S (s o is invalid)
Millisecond MS (invalid O)
----------------------------------------------

40. Last day of the month
S: Unknown
O: Select last_day (sysdate) value from dual

41. A day of the week (such as Sunday)
S: Unknown
O: Select next_day (sysdate, 7) vaule from dual;

42. String Conversion time
S: Can be directly converted to or select cast ('2017-09-08 'As datetime) Value
O: Select to_date ('2017-01-05 22:09:38 ', 'yyyy-mm-dd hh24-mi-ss') vaule from dual;

43. Calculate the difference of a part of the two dates (for example, seconds)
S: Select datediff (SS, getdate (), getdate () + 12.3) Value
O: Subtract directly with two dates (for example, d1-d2 = 12.3)
Select (d1-d2) * 24*60*60 vaule from dual;

44. Calculate a new date (for example, minute) based on the difference value)
S: Select dateadd (MI, 8, getdate () Value
O: Select sysdate + 8/60/24 vaule from dual;

45. Time in different time zones
S: Unknown
O: Select new_time (sysdate, 'ydt ', 'gmt') vaule from dual;

----- Time Zone parameter. Beijing must be in the east 8 zone of ydt -------
Ast ADT Standard Atlantic time
Bst bdt Standard Time
Standard Time for central CST CDT
Est EDT standard Eastern Time
GMT Standard Time
Hst hdt Alaska-Hawaii Standard Time
Standard Time for mst mdt Mountainous Areas
NST standard time for new Finland
Pst PDT Pacific Standard Time
Yst ydt Yukon Standard Time

 

1. String Functions
Length and Analysis

Datalength (char_expr) returns the number of characters in the string, but does not contain spaces

Substring (expression, start, length ).

Right (char_expr, int_expr) returns int_expr characters to the right of the string

Character operation class

Convert upper (char_expr) to uppercase

Lower (char_expr) to lowercase

Space (int_expr) generates int_expr Spaces

Replicate (char_expr, int_expr) copies the string int_expr times

Reverse (char_expr) reverse string

Stuff (char_expr1, start, length, char_expr2)

The length starting with start is replaced by char_expr2.

Ltrim (char_expr) rtrim (char_expr) removes Spaces

The ASCII (char) Char (ASCII) function corresponds to each other, and the ASCII code is used.

String search

Charindex (char_expr, expression) returns the starting position of char_expr

Patindex ("% pattern %", expression) returns the starting position of the specified mode. Otherwise, it is 0.

2. mathematical functions

ABS (numeric_expr) returns the absolute value

Ceiling (numeric_expr) is the smallest integer greater than or equal to the specified value.

Exp (float_expr) returns an index

Floor (numeric_expr) is less than or equal to the value of the specified maximum integer

Pi () 1415926 .........

Power (numeric_expr, power) returns power to power

Rand ([int_expr]) random number generator

Round (numeric_expr, int_expr) the precision specified by anint_expr is rounded

Sign (int_expr) returns + 1, 0,-1 based on positive number, 0, negative number.

SQRT (float_expr) Square Root

3. Date Functions

Getdate () returns the date

Datename (datepart, date_expr) returns a name such as June

Datepart (datepart, date_expr) is a part of the date.

Datediff (datepart, date_expr1.dateexpr2) date difference

Dateadd (datepart, number, date_expr)

In the preceding functions

Value and meaning of writing

YY 1753-March

QQ: 1-4

Mm 1-March December

Dy 1-366

DD 1-31

WK 1-54 weeks

DW 1-7 weeks

HH 0-23 hours

Mi 0-59 minutes

SS 0-59 seconds

MS 0-999 Ms

Date Conversion

Convert ()

 

4. system functions

Suser_name () User Login Name

User_name () name of the user in the database

User name in the database

The rule that show_role () applies to the current user.

Db_name () Database Name

Object_name (obj_id) Database Object Name

Col_name (obj_id, col_id) column name

Col_length (objname, colname) Column Length

Whether valid_name (char_expr) is a valid identifier
1. String Functions
Length and Analysis

Datalength (char_expr) returns the number of characters in the string, but does not contain spaces

Substring (expression, start, length ).

Right (char_expr, int_expr) returns int_expr characters to the right of the string

Character operation class

Convert upper (char_expr) to uppercase

Lower (char_expr) to lowercase

Space (int_expr) generates int_expr Spaces

Replicate (char_expr, int_expr) copies the string int_expr times

Reverse (char_expr) reverse string

Stuff (char_expr1, start, length, char_expr2)

The length starting with start is replaced by char_expr2.

Ltrim (char_expr) rtrim (char_expr) removes Spaces

The ASCII (char) Char (ASCII) function corresponds to each other, and the ASCII code is used.

String search

Charindex (char_expr, expression) returns the starting position of char_expr

Patindex ("% pattern %", expression) returns the starting position of the specified mode. Otherwise, it is 0.

2. mathematical functions

ABS (numeric_expr) returns the absolute value

Ceiling (numeric_expr) is the smallest integer greater than or equal to the specified value.

Exp (float_expr) returns an index

Floor (numeric_expr) is less than or equal to the value of the specified maximum integer

Pi () 1415926 .........

Power (numeric_expr, power) returns power to power

Rand ([int_expr]) random number generator

Round (numeric_expr, int_expr) the precision specified by anint_expr is rounded

Sign (int_expr) returns + 1, 0,-1 based on positive number, 0, negative number.

SQRT (float_expr) Square Root

3. Date Functions

Getdate () returns the date

Datename (datepart, date_expr) returns a name such as June

Datepart (datepart, date_expr) is a part of the date.

Datediff (datepart, date_expr1.dateexpr2) date difference

Dateadd (datepart, number, date_expr)

In the preceding functions

Value and meaning of writing

YY 1753-March

QQ: 1-4

Mm 1-March December

Dy 1-366

DD 1-31

WK 1-54 weeks

DW 1-7 weeks

HH 0-23 hours

Mi 0-59 minutes

SS 0-59 seconds

MS 0-999 Ms

Date Conversion

Convert ()

 

4. system functions

Suser_name () User Login Name

User_name () name of the user in the database

User name in the database

The rule that show_role () applies to the current user.

Db_name () Database Name

Object_name (obj_id) Database Object Name

Col_name (obj_id, col_id) column name

Col_length (objname, colname) Column Length

Whether valid_name (char_expr) is a valid identifier