---------Mathematical FunctionsSelect SQRT(2)--Open SquareSelect ABS(-123)--Zone Absolute ValueSelect ROUND(1.2345,1)--1-bit rounding retention after a decimal pointSelect COUNT(*)/5.0 fromCarSelect CEILING(COUNT(*)/5.0) fromCar--the smallest integer greater than this decimalSelect Floor(COUNT(*)/5.0) fromCar--The largest integer less than this decimal---------String FunctionsSelect LOWER('UserName')--uppercase letters converted to lowercaseSelect UPPER('UserName')--lowercase letters converted to uppercaseSelect LTRIM('name')--to compress the left spaceSelect RTRIM('name')--compress the space on the rightSelect Left(Name,3) fromCar--three characters from the left of car table nameSelect Right(Name,3) fromCar--three characters from the left of the car table name columnSelect SUBSTRING(Name,3,3) fromCar--3 characters starting from the third character of the car table name columnSelect REVERSE(Name) fromCar--reverses the string of the column in which it is locatedSelect CHARINDEX('Audi', Name) fromCar--find ' Audi ' in the car Table Name column which location appearsSelect REPLACE(Name,'Audi','BMW') fromCar--Replace ' Audi ' with ' BMW 'Select STUFF(Name,3,4,'BMW') fromCar--Replace the Name column with a ' BMW ' 4 characters from the beginning of the 3rd characterSelect RAND()*Ten --Random numbers that are randomly out of 0-1 can be *10 after random numbers between 0-10Select LEN('BMW')--take the length of the string----------Date-time functionsSelect GETDATE()--gets the current timeSelect Year(Birthday) fromInfo--year of date and time takenSelect MONTH(Birthday) fromInfo--month of date and time takenSelect Day(Birthday) fromInfo--number of days to take the date timeSelect DATEPART(Yy,birthday) fromInfo--Date Time yy years, mm months, dd days, hh hours, MI minutes, ss secondsSelect CAST(123 as varchar( -))--Data Conversion converts 123 to varchar
Common SQL functions