-
String functions:
● ASCII('a')=97---Returns the ASCII code corresponding to the letter a● CHAR('48')=0---Returns the character corresponding to the ASCII code 48● LCASE('ABcdE')="abced" or LOWER('ABcdE')="abced" (convert the given string to lowercase)
● UCASE('ABcdE')="ABCDE" or UPPER('ABcdE')="ABCDE" (convert the given string to uppercase)
● LTRIM(' fgf gh ')="fgf gh " (remove spaces to the left of the given string)
● RTRIM(' fgf gh ')=" fgf gh" (remove spaces 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, taking characters of the specified length)
● RIGHT('sde123-TRE',4)="-TRE" (starting from the right side of the given string, taking characters of the specified length)
● SUBSTRING('sde123-TRE',3,6)="e123-T" (starting from the specified position of the given string, taking characters of the specified length)
● CHARINDEX('23-','sde123-TRE')=5 (returns the first position where the first string appears in the second string, or returns 0 if it does not appear)
(Note: CHARINDEX('23T','sde123-TRE')=0)
(Note: CHARINDEX('TRE','DFTRE345TREDGS')=3 and CHARINDEX('TRE','DFTRE345TREDGS',4)=9, because the third parameter is not written, the search starts from the first character; otherwise, it starts from the first character Start searching at the position specified by the three parameters; the second example starts searching from position 4, and the first "TRE" found is at position 9)
● REVERSE('SDF463')="364FDS" (reverse the given string)
● REPLACE('abcdefghicde','cde','xx')="abxxfghixx" (replaces all second strings in first string with third strings)
● STR(123.457,5,2)="123.46" (Convert a number to a string. The first parameter is the number to be converted. The second parameter is the maximum length of the converted string. The third parameter specifies to several decimal places, please note that this conversion is rounded)
(Note: STR(123.457,2,2)="**"------because 5 is less than the length of 123.457)
● STUFF('I AM NIMMY,WEI COME!',6,5,'RWM')="I AM RWM,WEI COME!" (Delete a certain part of the given string and insert it into the specified string, no. One parameter is the string to be operated on; the second parameter is the starting position of the part to be deleted; the third parameter is the length of the part to be deleted; the fourth parameter is the string to be inserted)
(Note the difference between it and REPLACE: STUFF('ERTY23TY',3,2,'K')='ERK23TY" and REPLACE('ERTY23TY','TY','K')="ERK23K")
● REPLICATE('ab',6)="abababababab" (returns the specified number of specified strings)
● SPACE(6)=" "(returns the specified number of spaces)
*************************************************** *********
Date functions● DATEPART() returns the specified part 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" (returns the date after or before the given date. The first parameter is the increasing scale, which can be year, month, day, hour, minute and second. <See above>, the second parameter is the incrementing number, and the third parameter is the given date)
● DATEDIFF(dd,'2003-12-23','2003-12-27')=4 (returns the difference between two given dates. The first parameter specifies the scale of the difference, which can be year, month, day, hour, minute and second< See above >, the second parameter is the start date, the second parameter is the end date)
● DATENAME(dw,'2003-12-27')="Saturday" (returns the specified name of the given date. The first parameter specifies the name type to be returned, which can be year, month, day, week, hour, minute and second <see above>, The second parameter is the given date)
*************************************************** *************
Math functions● ABS(-2)=2 Returns the absolute value of a number● Returns the smallest integer greater than or equal to the parameter
CEILING(1.2)=2/CEILING(1.7)=2/CEILING(2)=2
CEILING(-1.2)=-1/CEILING(-1.7)=-1/CEILING(-1)=-1
● Returns the largest integer less than or equal to the parameter
FLOOR(1.2)=1/FLOOR(1.7)=1/FLOOR(1)=1
FLOOR(-1.2)=-2/FLOOR(-1.7)=-2/FLOOR(-2)=-2
● Returns the number of decimals rounded to the specified number of digits
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/ Returns the square of the parameter ● SQRT(9)=3/ Returns the square root of the parameter ● SIGN(0.01)=1/SIGN(1000)=1/SIGN(0)=0/SIGN(-0.3 )=-1/SIGN(-10000)=-1
Determine whether the parameter is positive, negative or 0