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