T-SQL (4)-function function (RUI)

functions in SQL Server • String functions • Date functions • Mathematical functions • System functions these are equivalent to the internal functions in C, let's look at one: the String function Date functionSELECT DATEPART( Day,'01/15/2000') returns:15
the integer form of the date part specified in the dateDATEPARTSELECT GETDATE() Return: Today's date gets the current system dateGETDATEexample Description of function nameSELECT DATEADD(MM,4,’ on/ on/ About') return: Returns in the current date format/ on/ Aboutadds the specified value to the date after the specified date partDATEADDSELECT DATEDIFF(MM, ' -/ on/ About’,‘ One/ on/ About') return:4The difference between a specified date part of two datesDATEDIFFSELECT Datename(DW,'01/01/2000'returns: A string form of the date part specified in the Saturday dateDatenameMathematical FunctionsSELECT SQRT(9) returns:3
takes the square root of a floating-point expressionSqrtSELECT ABS(- +) returns: +take the absolute value of a numeric expressionABSexample Description of function nameSELECT CEILING(43.5) returns: -returns the smallest integer greater than or equal to the given number expressionCEILINGSELECT POWER(5,2) returns: -take the power value of a numeric expressionPOWERSELECT ROUND(43.543,1) returns:43.5rounding a numeric expression to a specified precisionROUNDSELECT  Sign(- +) returns:-1for positive returns+1, for negative returns-1, returns 0 for 0 SignSELECT  Floor(43.5) returns: +takes the largest integer less than or equal to the specified expression Floorsystem FunctionsSELECT user_name(1) returns: Returns "dbo" from any database


Returns the user name from the given user i duser_nameSELECT CONVERT(VARCHAR(5)，12345) Return: String 12345 is used to transform the data typeCONVERTexample Description of function nameSELECT Current_Userreturn: The user name you logged in returns the name of the current userCurrent_UserSELECT datalength('China League a') returns:7returns the number of bytes used to specify an expressiondatalengthSELECT host_name() returns: The name of the computer to which you are logged in returns the name of the computer to which the current user is logged onhost_nameSELECT System_userreturn: The username you are currently logged in returns the name of the user who is currently logged inSystem_userCase Study 1-Requirements • A company printed a number of prepaid cards, the card's password is randomly generated, and now the problem: the card inside the "O and 0" (oh and 0) "I and 1" (Ah and a), the user reflects that it is unclear, the company decided to store the password in the database all the "Oh" Change to " 0 "and change all the" I "to"1please write the SQL statement to achieve the above requirements; • Database table name: Card; password field name: PassWord; case study 1-Analysis • This is an UPDATE statement that requires the use of an updated statement; • Because the words are involved Replacement of the string, you need to use the function Replace in SQL Server; case study 1-t-SQL two-line SQL statementUpdateCardSetPassWord= Replace(PassWord,'O','0')UpdateCardSetPassWord= Replace(PassWord,'I','1') row of SQL statementsUpdateCardSetPassWord= Replace(Replace(PassWord,'O','0'),'I','1'Case Study 2-Requirements • Have the following character data in a database table, such as: --1、 --2、 --3、 --Ten、 -- -、 --108、 -- -、 -- One、 -- the、 --1、 --2now you want to sort through the SQL statements, and first sort by the numbers in the first half, then the numbers in the second half , and the output is sorted like this: --1、 --2、 --3、 --Ten、 -- One、 -- the、 -- -、 -- -、 --108、 --1、 --2• Database table name: sellrecord; field name: Listnumber; case study 2-analysis • This is a query statement that requires the use of the SELECT statement • Ordering by using the order by, and the Ord In the row sequence of ER by, it is also necessary to recalculate the numbers of the first half of the sorted numbers, which can be binate found "-The position of the symbol, then, take its left half, and then use the CONVERT function to convert it to a number:Convert(int, Left(Listnumber,CharIndex('-', Listnumber)-1) • The second half of the number, you can first find the "-The position of the symbol, then replace all characters from the first position to that position with a space, and then convert it to a number using the CONVERT function: Case study 2-t-SQLConvert(int,Stuff(Listnumber,1,Charindex('-', Listnumber),"'))SELECTListnumber fromSellrecordORDER  byConvert(int, Left(Listnumber,CharIndex('-', Listnumber)-1)),Convert(int,Stuff(Listnumber,1,Charindex('-', Listnumber),"'Of course, there are other ways to calculate the sequence, and you can think for yourself. 

T-SQL (4)-function function (RUI)