SQL function summary

xiaoxiao2021-03-30  186

String function:

● ASCII ('a') = 97 --- Returns the ASCII code corresponding to the letter A

● Char ('48 ') = 0 --- Returns 48 Characters corresponding to this ASCII

● LCASE ('Abcde') = "Abced" or Lower ('Abcde') = "Abced" (turn the given string to lowercase)

● ucase ('Abcde') = "Abcde" or Upper ('Abcde') = "Abcde" (turn the given string to uppercase)

● LTRIM ('fgf gh') = "fgf gh" (removed the space left on the left side of the given string)

● RTRIM ('fgf gh') = "fgf gh" (removed the space on the right side of the given string)

● LEN ('SDE123-TRE') = 10 (Returns the length of the given string)

● LEFT ('SDE123-TRE', 5) = "SDE12" (starting from the left side of the given string, take the specified length)

● Right ('SDE123-TRE', 4) = "- TRE" (starting from the right side of the given string, take the specified length character)

● Substring ('SDE123-TRE', 3, 6) = "E123-T" (starting from the specified location of the given string, take the character of the specified length)

● Charindex ('23- ',' sde123-TRE ') = 5 (returns the first string appearing in the second string, if not, return 0)

(Note: Charindex ('23T', 'SDE123-TRE') = 0)

(Note: Charindex ('TRE') = 3 and Charindex ('TRE', 'DFTRE345TREDGS', 4) = 9, because the third parameter is not written

Find from the first character; otherwise start looking for the position specified from the third parameter; the second example is starting from the 4th bit, found the first "TRE" located 9th)

● Reverse ('SDF463') = "364fds" (reverse the given string)

● Replace ('AbcdefghicDe') = "Abxxfghixx" (replaced all the second string in the first string) with a third string)

● STR (123.457, 5, 2) = "123.46" (converted the number to a string, the first parameter is the number to be converted, the second parameter is the maximum length of the string after the conversion, the third parameter designation Keep a few decimals, please note that this conversion is rounded from

(Note: STR (123.457, 2, 2) = "**" ------ because the length of 5 is less than 123.457)

● Stuff ('I am Nimmy, WEI Come!', 6, 5, 'RWM') = "I am RWM, Wei Come!" (Deleted a part of the given string and inserted into the specified string, One parameter is a string to be operated; the second parameter is the starting position to delete the portion; the third parameter is to delete the length of the portion; the fourth parameter is the string to be inserted) (note it and the replace Difference: Stuff ('Erty23TY', 3, 2, 'K') = 'ERK23ty "and Replace (' Erty23ty ',' Ty ',' K ') =" ERK23K ")

● Replicate ('ab', 6) = "abababababab" (Returns the specified specified string)

● Space (6) = "" (Returns the specified number of spaces)

*********************************************************** **********

Date function

● DATEPART () Returns the specified section of the given date

DatePart (YY, '2003-12-27') = 2003 ------- Year / YYYY

Synonymous function: Year ('2003-12-27') = 2003

DatePart (mm, '2003-12-27) = 12 ---- Month / M

Synonymous function: Month ('2003-12-27') = 12

DatePart (DD, '2003-12-27') = 27 ---- Day / d

Synonymous function: day ('2003-12-27') = 27

DatePart (dw, '2003-12-27') = 7 ----- Weekday (the day is the seventh day of the week, that is, Saturday)

DatePart (HH, '13: 55: 34 ') = 13 ------ Hour

DatePart (MI, '13: 55: 34 ') = 55 ------ Minute / N

DatePart (SS, '13: 55: 34 ') = 34 ------ Second / S

● DATEADD (MM, 2, '2003-12-27') = "2003-2-27" (After returning to a given date, the first parameter is incremented, which can be the day and month. , the second parameter is increasing, the third parameter is a given date)

● Datediff (DD, '2003-12-23', '2003-12-27) = 4 (return two given dates, the first parameter indicates the difference in difference, which can be the month of the day and month See>, the second parameter is the start date, the second parameter is the end date)

● Datename (DW, '2003-12-27') = "Saturday" (return to the specified name of the given date, the first parameter indicates the name type to return, can be the day of the year, the day, the second ", The second parameter is a given date)

*********************************************************** ************

Mathematical function

● ABS (-2) = 2 Returns an absolute value of a number ● Return to the minimum integer equal to parameters

CEILING (1.2) = 2 / CEILING (1.7) = 2 / ceiling (2) = 2

CEILING (-1.2) = - 1 / CEILING (-1.7) = - 1 / ceiling (-1) = - 1

● Returns the maximum integer of equal to or equal to parameters

FLOOR (1.2) = 1 / floor (1.7) = 1 / floor (1) = 1

FLOOR (-1.2) = - 2 / floor (-1.7) = - 2 / floor (-2) = - 2

● Return the decimal number of the specified number of points rounded

Round (1.2,0) = 1 / round (1.7, 0) = 2 / round (2,0) = 2

Round (-1.2, 0) = - 1 / round (-1.7, 0) = - 2 / round (-2, 0) = - 2

Round (1.3536, 2) = 1.35 / round (1.3536, 3) = 1.354

● Square (9) = 81 / Return parameter square

● SQRT (9) = 3 / Return parameter square root

● SIGN (0.01) = 1 / Sign (1000) = 1 / Sign (0) = 0 / Sign (-0.3) = - 1 / siGn (-10000) = - 1

The judgment parameter is positive, negative or 0

转载请注明原文地址:https://www.9cbs.com/read-130597.html

New Post(0)