Database (iv)

String Functions 
1. ascii--returns the first letter ASCII encoding of the string
Select ASCII (name) from haha
Select *from haha where ASCII (name) >200
2 、--convert ASCII code to corresponding character
Select CHAR (100)
Select CHAR (age) from haha
3 、--Find a string that returns the index of the first letter of the qualifying index starting at 1
Select CHARINDEX (' EFG ', ' abcdefghijklmnopqrstuvwxyz ')
Select CHARINDEX (' 199 ', CID) from haha
4 、--string concatenation
Select ' abc ' + ' def ' + ' ghi ' + ...
5 、--return similarity
Select difference (' abcdefghijklmn ', ' ABCDE ')
6 、--Intercept string from left to right to specify length
Select Left (' ABCdef ', 3)
7 、--Returns the length of the string, the following space does not count
Select LEN (' ABCD ')
8 、--converted to lowercase
Select LOWER (' Abcdefghig ')
9 、--Remove the left space, which is the preceding space in the string, equivalent to TrimStart in. NET
Select LTRIM (' CCCCCCCC d ')
the 、--lookup string returns the index, functionally equivalent to Charindex
Select PATINDEX ('%gh% ', ' abcdefghijklmn ')
One 、--substitution, the first parameter is the target string, the second argument is the target segment to replace, and the third argument is the replaced content
Select REPLACE (CID, ' 606 ', ' 103 ') from haha
、--Copy, and can replicate 0~n times, equivalent to cutting and then copying the process
Select REPLICATE (' abc ', 3)
、--flipping a string
Select REVERSE (' abcdefghijklmnopqrstuvwxyz ')
Select REVERSE (name) from haha
、--intercept a string from right to left to specify length
Select Right (' ABCDEFG ', 3)
、--to the right of the space
Select RTRIM (' ABC d ')
、--print space, the parameter represents the number of spaces
Select ' A ' +space (Ten) + ' B '
、--Converts a decimal number to a string, the first parameter is the source data for the decimal, the second argument is the length of the string, and the third parameter is the number of digits after the
Select STR (2131.23232,6,1)
、--Delete the specified length of character at the beginning of the specified index, insert the specified character at the specified index, the first argument is the original string, the second parameter is the index start position, the third parameter is the length of the specified deletion, and the fourth parameter is the specified inserted string
Select STUFF (' abc123 ', 4,0, ' HelloWorld ')
、--intercepts a specified length of string from the specified index
Select SUBSTRING (cid,7,8) from haha
convert 、--to uppercase
Select UPPER (' ABCDEFG ')

date and Time functions
1 、--System constant, returns a value directly
SELECT @ @DATEFIRST
2 、--Add a time period to the designation
Select DATEADD (year,2, ' 2006-07-31 ')
3 、--The time difference, you can specify the type to calculate
Select DATEDIFF (Day, ' 2011-07-18 ', ' 2014-06-02 ')
4 、--Return a time value, you want to get the number of a block type, such as: years, months, days, hours, minutes, seconds, can be obtained separately
Select Datename (year, ' 2013-05-06 ')
5 、--is equivalent to datename, except that datepart returns an int type, DATENAME returns a string type
Select DATEPART (year, ' 2014-11-02 ')
6 、--The time in the returned date is the day of the month, and the int value is returned
Select Day (' 2014-11-22 ')
7 、--Gets the date time of the server on which the current database resides
Select GETDATE () as normal
8 、--Determine if the time is the date, if 1 is returned correctly, if the error returns 0
Select ISDATE (' 2013-02-22 ')
9 、--to the month of the time value
Select MONTH (' 2014-11-02 ')
、--Get the current system time
Select Sysdatetime ()
、--Year of acquisition time
Select year (' 2014-11-02 ')

Type conversions, conversion functions
1. CAST
Select CAST (123 as varchar (20))
Select CAST (1.23 as int)
Select CAST (123 as Decimal (18,2))
Select CAST (' 12.3 ' as Decimal (18,2))
2. CONVERT
--Previous Write data type, followed by data
Select CONVERT (int, ' 123 ')

Database (iv)