sql-Math, String, time-date functions, and type conversions

--Mathematical functions
--abs Absolute Value,
Select ABS ( -99)
--ceiling to take the upper limit,
Select CEILING (4.5)
--floor to the lower limit
Select Floor (4.5)
--power several parties,
Select POWER (2,2)
--round rounded,
Select Round (6.45,1)
--sqrt Open Square
Select SQRT (9)
--square Square
Select SQUARE (5)

--String function
--ascii returns the leftmost character of the string ASCII code
Select ASCII (' name ')
Select ASCII (name) from Student1--View the ASCII code for the first character of the owner's name
--char convert ASCII code to characters
Select CHAR (.)
Select CHAR (Chinese) from Student1--speak all language fractions converted to characters
--note that the expression or constant that is converted (integer) needs to be between 0-256 and out of the output n

--len returns the length of a string
Select LEN (' Asdfghh ')
Select LEN (name) from Student1 --Displays the length of all names

--charindex returns the index of the first character of a string that appears at the beginning of a string
Select CHARINDEX (' d ', ' ASDFGHHJKKHG ')--index starting from 1
Select CHARINDEX (' All ', age) from Kaoshi --View the index that appears in 23

--difference return similarity degree with 0--4
Select difference (' ASDDFGHJK ', ' adfjkgh ')
--left means the string is truncated from the left
Select Left (' Asfgdsssdgh ', 4)

--right from the right.
Select Right (' Asfgdsssdgh ', 4)

--lower all converted to lowercase
Select LOWER (' ADASJFKSKDFFSFA ')

--upper Caps
Select UPPER (' Afadsfasfsdfssad ')

--ltrim Remove the left space
Select LTRIM (' ASD ')
--rtrim Remove the right space
Select RTRIM (' ASD ')

--patindex equivalent to the first character index bit in the character where the charindex returned the string
Select PATINDEX ('%dasda% ', ' 154dasda546 ')

--replace Find replacement
select REPLACE (Sex, ' female ', ' girl ') from Student1--display only, do not change
--replicat copy and paste
select REPLICATE (' ASD ', 3)--altogether three times
--reverse Flip
select REVERSE (' ASDFGGHJK ')

--space spaces
Select ' A ' +space (5) + ' BC '

--str cast to String
Select STR (123456.222,5,1)--Parameter 1 is the value to be converted, and parameter 2 is the length retained after conversion
--Parameter 3 is the number of digits to be retained after the decimal point
--Note that parameter 2 cannot be converted when it is less than the integer portion of the parameter 1

--stuff
--from the position of the first few indexes, See if you need to delete a few backwards, and then insert what you want to insert into the
--parameter 1 is the string that needs to be inserted
--Parameter 2 is the beginning of the first few indexes

--Parameter 4 is the newly inserted character

--substring
--intercept string
--Parameter 1 is the truncated string
--the index from which the parameter 2 starts
--Parameter 3 is the length of the Intercept
Select SUBSTRING (' 151111111111111111111111888861 ', 1,4)

--1. Time-Date function:
Set Datefirst 1 --setting Monday as the first day
--datepart function to return a part of a time date
--Parameter 1 refers to the return of the part
--Parameter 2 means returning from that time date
--datefirst is a system constant that needs to be added with @@
SELECT @ @DATEFIRST as ' 1st day ', DATEPART (Dw,getdate ()) as ' Tobay '
Select GETDATE ()--execution is after system time
-the type that needs to be changed , the quantity that needs to be changed, the time and date to change

Select DATEADD (hh,5, ' 2015-12-12 ')
--datediff Calculate the time difference, different different, difference
--the type that needs to be changed, the start date, the end time date
Select DateDiff (MM, ' 2013-12-31 ', ' 2014-1-3 ')
DECLARE @startday varchar--you need to add a data type when declaring a variable.
Set @startday = ' 1993-11-1 '--Sets the value of the variable
Select DATEDIFF (Day, @startday, GETDATE ())--as ' online time '
Select Day (' 1993-11-1 ')
Select MONTH (' 1993-11-1 ')
Select year (' 1993-11-1 ')
--datepart Returns a portion of a time date
--Parameter 1 is which part of the return
--the date on which the parameter 2 is calculated
Select DATEPART (year, ' 2009-9-8 ')--Return years
Select DATEPART (DY, ' 2009-9-8 ')--the first day of the year to return to DayOfYear
Select DATEPART (QQ, ' 2009-9-8 ')--Return to quarterly total of four quarters
--Determine if the date time is correct
Select ISDATE (' 2012-2-29 ')--Returns correctly 1
Select ISDATE (' 2011-2-29 ')--error returned 0
--2. Type conversions:

--Data type conversion cast convert
--cast writes the converted value + as + the type converted to
Select CAST (1.999 as int)
Select CAST (1.56656 as varchar )
Select CAST (1.3996646 as Decimal (18,2))
Select cast (CAST (' 1.5656 ' as Decimal (18,2) as int)
Select CAST (' 1.5656 ' as Decimal (18,2))
The --convert parameter 1 is the type that needs to be converted, and parameter 2 is the value that needs to be converted
Select CONVERT (int, ' a ')
Select CONVERT (Decimal (18,2), ' 89.9912 ')--automatically rounded when the exact number of digits is later
Select CONVERT (varchar (), 3.1415926)

sql-Math, String, time-date functions, and type conversions