Functions in SQL Server database

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　String functions

Name of function	Describe	Example
CHARINDEX	Finds the starting position of a specified string in another string	SELECT CHARINDEX (' Jbns ', ' My Jbns Course ', 1) Returns:4
Len	Returns the length of the string passed to it	SELECT LEN (' SQL Server course ') return:
UPPER	Converts the string passed to it to uppercase	SELECT UPPER (' SQL Server course ') Back to:SQL SERVER course
LTRIM	Clear the space to the left of the character	SELECT LTRIM (' Zhou Zhiyu ') return: Zhou Zhiyu (trailing spaces reserved)
RTRIM	Clear the space to the right of the character	SELECT RTRIM (' Zhou Zhiyu ') return: Zhou Zhiyu (previous space reserved)
Right	Returns the specified number of characters from the right side of the string	SELECT right (' buy and sell ' . Tourson ', 3) return: Tourson
REPLACE	Replace a character in a string	SELECT REPLACE (' mo le can be cut . did ', ' can be ', ' LAN ') Return: Molland cut . Yang Lan
STUFF	In a string, delete the specified length of character and insert a new string at that location	SELECT STUFF (' ABCDEFG ', 2, 3, ' My Music My World ') Back to:A My Music My World EFG
Left	Intercepts a specified length of character from the left of the string	SELECT left (' ABCDEFG ', 3) return:ABC
Right	Intercepts a specified length of character from the right of the string	SELECT right (' ABCDEFG ', 3) Back to:EFG

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　Date function

Name of function	Describe	Example
GETDATE	Get the current system date	SELECT GETDATE () Return: Today's date
DATEADD	Adds the specified value to the date after the specified date part	SELECT DATEADD (mm,4, ' 01/01/2009 ') Return: Returns 05/01/2009 in the current date format
DATEDIFF	Interval between two dates for a specified date part	SELECT DATEDIFF (mm, ' 01/01/2009 ', ' 05/01/2009 ') Returns:4
Datename	The string form of the date part specified in the date	SELECT datename (DW, ' 01/01/2000 ') Back:Saturday or Saturday
DATEPART	The integer form of the date part specified in the date	SELECT DATEPART (Day, ' 01/15/2000 ') return:

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　Mathematical functions

Name of function	Describe	Example
RAND	Returns a random float value from 0 to 1	SELECT RAND () return:0.79288062146374
Abs	Take the absolute value of a numeric expression	SELECT ABS (-43) return:
CEILING	Takes the smallest integer greater than or equal to the specified numeric value, expression	SELECT CEILING (43.5) return:
Floor	Takes the largest integer less than or equal to the specified expression	SELECT Floor (43.5) return:
POWER	Take the power value of a numeric expression	SELECT POWER (5,2) Back to :
ROUND	Rounding a numeric expression to a specified precision	SELECT ROUND (43.543,1) return:43.500
Sign	Returns +1for positive numbers, 1fornegative returns , and 0 for 0	SELECT sign (-43) return:-1
SQRT	Takes the square root of a floating-point expression	SELECT SQRT (9) Returns:3

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　System functions

Name of function	Describe	Example
CONVERT	Used to transform data types	SELECT CONVERT (VARCHAR (5),12345) Return: string 12345
Current_User	Returns the name of the current user	SELECT Current_User Return: The user name you logged in
Datalength	Returns the number of bytes used to specify an expression	SELECT datalength (' China A Union ') Returns:5
Host_name	Returns the name of the computer to which the current user is logged	SELECT HOST_NAME () Return: The name of the computer you are logged on to
System_user	Returns the name of the user who is currently logged on	SELECT System_user Return: The name of the user you are currently logged into
User_name	Returns the user name from the given user ID	SELECT user_name (1) Return: Return "dbo" from any database

Functions in SQL Server database