SQL Server Common functions

Meta-data functions

db_id
Gets the database unique identity, which is the unique identity on the server
Grammatical structure: db_id ([' database_name '])
Parameter description: database_name is the database name and is an optional parameter. Returns the ID of the current database if not specified
Return value: Database identifier of type int
Use: SELECT db_id ();

Db_name
Gets the name of the current database
Grammatical structure: db_name ([' database_id '])
Parameter description: database_id is an optional parameter, is the ID of the database, can be obtained by the DB_ID function, and if not specified, returns the name of the currently located database
Return Value: Database name
Use: SELECT db_name ();

object_id
Gets the database object identifier, which is a unique identity in the database
Grammatical structure: object_id (' [database_name.[ schema_name].| Schema_name.] object_name ' [, ' object_type '])
Parameter description:

Name	Description	is optional
database_name	Database name	Options available
Schema_name	Schema name	Options available
object_name	Object Name	Must-Choose
Object_type	Object type	Options available

Return value: An object identifier of type int
Use:
IF object_id (N ' dbo. User ', N ' U ') is not NULL
DROP TABLE dbo. User;
GO

object_name
Get Database object Name
Grammatical structure: object_name (object_id)
Parameter description: OBJECT_ID is the ID of the database object and can be obtained by object_id
Return value: Database object name

ObjectProperty
Gets the specified property value for the specified database, specified object.
Grammatical structure: objectproperty (Id,property)
Parameter description:
ID represents the ID of the specified database, object
property represents the attribute to be returned

Common Property attribute values

Property value	Description	return value
Tablehastextimage	Whether the table contains a column of text, image data type	int 1 means true 0 indicates false
Tablehasprimarykey	Whether the table contains a primary key	int 1 means true 0 indicates false
Tablehasindex	Whether the table contains an index	int 1 means true 0 indicates false
Tablehasforeignkey	Whether the table contains foreign keys	int 1 means true 0 indicates false
SchemaID	The schema ID of the object	Int
ownerID	Object owner	Int
Isview	Whether it is a view	int 1 means true 0 indicates false
Isusertable	is a user-created table	int 1 means true 0 indicates false
Istable	Whether it is a table	int 1 means true 0 indicates false
Issystemtable	is the system table	int 1 means true 0 indicates false
IsPrimaryKey	Whether it is a primary key	int 1 means true 0 indicates false

Aggregation functions

Avg
Calculates the average of the map set, which is ignored if the parameter value is NULL
Grammatical structure: AVG ([all | distinct] expression)
Parameter description:
All: As the default value, the identity calculates the average for all data
Distinct: de-weight, same value only as one calculation
Expression: Expressions (columns). Subqueries and other aggregate functions are not allowed inside an expression.
Use: Select Ave (age) from student

MIN
Calculates the minimum mapping set, which can be used for numeric, char, varchar, or datetime, money, or smallmoney columns, but not for a bit column, ignoring null values
Grammatical structure: MIN ([all | distinct] expression)
Parameter description: Same as Avg
Use: Select min (avg) from student

MAX
Calculates the maximum value in the map set. Like the Min function, available for numeric, char, varchar, or datetime, money, or smallmoney columns, but not for bit columns, ignoring null values
Syntax structure: MAX ([all | distinct] expression)
Parameter description: Same as Avg
Use: Select Max (age) from student

SUM
For summation, only for numeric types (except for bit types), ignoring null values
Syntax structure: SUM ([all | distinct] expression)
Parameter description: Same as Avg
Use: Select SUM (grade) from class

COUNT
The number of data items that are used to calculate conditions
Syntax structure: COUNT ([[all | distinct] expression] | *)
Parameter description: expression is anything other than text, image, or ntext, but is not allowed to be an aggregate function and subquery
Use:
COUNT (*) returns the number of all column items, including columns with null and duplicates
Count (all expression) returns the number of columns that are NOT null values
COUNT (distinct expression) returns the number of columns with a unique non-null value

STDEV
Calculate standard deviation
Grammatical structure: STDEV ([all | distinct] expression)
Parameter description: expression must be a numeric type (not including bit type), no aggregate functions and subqueries are allowed, null values are ignored
Use: Select Stdev (age) from student

Var
Calculate Variance
Syntax structure: VAR ([all | distinct] expression)
Parameter description: Same STDEV
Use: Select VAR (age) from student

Mathematical functions
Abs
Calculate absolute Value
Grammatical structure: ABS (expression)
Parameter description: expression must be a numeric type (except for bit type)
Use: Select ABS (-1)

Celling
Gets the integer value greater than or equal to the minimum. Commonly known as the ceiling function, as far as possible to the largest rounding.
Grammatical structure: celling (expression)
Parameter description: With ABS
Use: Select Celling (15.2)--Output 16

Floor
Gets the value of less than or equal to the maximum integer. Relative to the celling function, commonly known as the floor function, as far as possible to the smallest rounding
Syntax structure: floor (expression)
Parameter description: With ABS
Use: Select Floor (15.6)--Output 15

ROUND
Gets the value of the specified length and precision
Grammatical structure: ROUND (expression,length[, Function])
Parameter description:
Expression-Numeric expressions
The precision of length rounding. Positive number-keep a few digits after the decimal point and a negative number before the decimal point
function truncates decimals. The value is tinyint, smallint, or int. The value is omitted or rounded to 0. Truncates decimals If a value of 0 is specified
Use:
Select Round (123.666,0)--124.00
Select Round (123.66,0,1)--123.00

RAND
Returns a floating-point number from 0 to 1
Syntax structure: RAND ([seed])
Parameter description: Seed is a seed value and is an expression of an integer type (tinyint, smallint, int).
Use: Select rand ()

String functions

Ascii
Gets the ASCII encoding of the specified character
Syntax structure: ASCII (expression)
Parameter description: expression is a char or varchar type.
Use: Select ASCII (' K ')--107

CHAR
Inverse operation of the ASCII function, which obtains the corresponding character through the ASCII code
Syntax structure: CHAR (integer_expression)
Parameter description: integer_expression is an integer-type expression
Use: Select char (107)--k

Unicode
Gets the Unicode encoding of the specified character
Syntax structure: Unicode (expression)
Parameter description: expression is a nchar or nvarchar type
Use: Select Unicode (' K ')

NCHAR
Inverse operation of Unicode functions, with Unicode code to obtain corresponding characters
Grammatical structure: NCHAR (integer_expression)
Parameter description: integer_expression is an integer-type expression
Use: Select NCHAR (107)

PatIndex
Gets the position of the first occurrence of the string, and returns 0 if no match is reached.
Grammatical structure: PatIndex ('%pattern% ', expression)
Parameter description:
Pattern: Can be a string, or you can use a wildcard character. If you use wildcards you need to pair
Expression: Character expressions
Return value: If the expression data type is varchar (max) or nvarchar (max) is bigint, otherwise int
Use: Select Patindex ('%llo% ', ' Hello world! ')

SPACE
Generate spaces
Grammatical structure: SPACE (integer_expression)
Parameter description: Integer_expression means generating several spaces
Use: Select Space (3)

REPLICATE
Generates a string repeatedly at a specified number of times
Grammatical structure: REPLICATE (character_expression,integer_expression)
Parameter description:
Character_expression the string to be generated
Number of integer_expression generated
Use: Select Replicate (' Hello ', 5)

SUBSTRING
Intercept string, index starting at 1
Grammatical structure: SUBSTRING (expression,start,length)
Parameter description:
Expression can be a string, a binary string, a literal, an image, or an expression that contains a column, but cannot use an aggregate function
Start to specify the substring start position
Length returns the number of characters that cannot be negative
return value:
If expression is a supported character data type, character data is returned, and binary data is returned if expression is a supported binary data type
Use: Select substring (' hello ', +)

Len
Gets the length of the string, but does not include the right space.
Grammatical structure: LEN (string_expression)
Parameter description: string to calculate length
Return value: The expression data type is varchar (max), nvarchar (max), or varbinary (max) is bigint. otherwise an int
Use: Select LEN (' Wang Nima ')

STUFF
Removes a string of the specified length from the specified string and inserts another set of characters at the starting point
Grammatical structure: STUFF (source_character_expression,start,length,destination_character_expression)
Parameter description:
Source_character_expression the source string. Can be a constant, a variable, or a character column or a binary row of data
Start specifies the start position of the delete and insert. If start or length is negative, an empty string is returned. Returns an empty string if start is longer than the source string
length specifies the number of characters to delete. If length is longer than the source string, delete all
Destination_character_expression the new string to insert. Can be a constant, a variable, or a character column or a two-level row of data
Use: Select Stuff (' abcdef ', 3,2, ' 111 ')--ab111ef

CHARINDEX
Used to search for a specific string in the specified string, and to specify where to start the search, returning where the target character was first found
Grammatical structure: CHARINDEX (Expression1,expression2[,start_location])
Parameter description:
Expression1 expression of the string to find
Expression2 A string expression that specifies the search, which can be a character column
Start_location the search location of the searched string. If the value is negative or 0, search from the beginning
Return value: bigint if the data type of expression2 is varchar (max), nvarchar (max), or varbinary (max). otherwise an int
Use: Select CHARINDEX (' WC ', ' wk wr WC ', 2)

QUOTENAME
Generating a Unicode string with delimiters
Grammatical structure: QUOTENAME (' character_string ' [, ' Quote_character '])
Parameter description:
Character_string:unicode string
Quote_character: A single string used as a delimiter, and the default is "[]"
return value: nvarchar (258)
Use:
Select QuoteName (' Hello ')--[hello]
Select QuoteName (' Hello ', ' | ') --|hello|

Str
Used to convert floating-point numbers to strings
Grammatical structure: STR (float_expression [, Length[,decimal]])
Parameter description:
Float_expression expression of floating-point numeric value
Length total. It includes decimal points, symbols, numbers, and spaces. The default value is 10
The number of digits after the decimal point. Decimal must be less than or equal to 16, and the result will be truncated if it is greater than 16
return value: Char
Use:
Select STR (123.987) –124 str function is automatically rounded
Select STR (123.987,6) – Two spaces +124 automatically fills spaces because decimal is not specified
Select STR (123.987,6,4)--123.99

Left
Truncate left string, effect equivalent to substring (expression,1,length)
Syntax structure: Left (character_expression,integer_expression)
Parameter description:
character_expression a string or binary expression, which can be a constant, a variable, or an expression.
Integer_expression positive integer specifying the number of characters returned
return value: varchar or nvarchar
Use: Select Left (' wow ', 2)--wo

Right
Truncate right string, effect equivalent to substring (expression,len (expression)-length+1,length)
Grammatical structure: Right (character_expression,integer_expression)
Parameter description: Same as Left function
return value: Same as Left function
Use: Select right (' Wow ', 2) –w

LTRIM
To clear the left space character
Grammatical structure: LTRIM (character_expression)
Parameter description: character_expression is a character or binary data expression that allows you to make a constant, variable, or data column
return value: varchar or nvarchar string
Use: Select LTrim (' 123 ')

RTRIM
To clear the right space character
Grammatical structure: RTRIM (character_expression)
Parameter description: Same LTrim
return value: Same as LTrim
Use: Select RTrim (' 123 ')

LOWER
Converts all specified strings to lowercase characters
Grammatical structure: LOWER (character_expression)
Parameter description: character_expression is a character or binary data expression that allows you to make a constant, variable, or data column
return value: varchar or nvarchar string
Use: Select lower (' aBc ')--abc

UPPER
Converts all specified strings to uppercase characters
Grammatical structure: UPPER (character_expressiion)
Parameter description: Same lower
return value: Same as lower
Use: Select Upper (' aBc ') –abc

REVERSE
Invert (reverse order) the specified string
Grammatical structure: REVERSE (character_expression)
Parameter description: character_expression is a character or binary data expression that allows you to make a constant, variable, or data column
return value: varchar or nvarchar string
Use: Select reverse (' abc ') –CBA

Datalength
Gets the number of bytes for the specified string. It is not only suitable for string type data, but also suitable for any type of data such as text (text, ntext), binary data (varbinary, binary), and images (image).
Grammatical structure: datalength (expression)
Return value: Returns bigint if the expression data type is a varchar (max), nvarchar (max), or varbinary (max) data type. Otherwise, int is returned
Use: Select Datalength (' i ') –2

SOUNDEX
Returns a four-character code to evaluate the similarity of two strings
Grammatical structure: SOUNDEX (' character ')
Use:
Select Soundex (' ABCDE ')--a120
Select Soundex (' abcdf ')--a122

Difference
Returns an integer value that represents the difference between the SOUNDEX values of a two-character expression
Grammatical structure: difference (expression1,expression2)
Use: Select Difference (' abc ', ' ABF ')

REPLACE
Replaces the specified character in the specified string
Syntax structure: REPLACE (String_expression1,string_expression2,string_expression3)
Parameter description:
String_expression1 the string expression to search for. Can be either a character or a binary data value
String_expression2 the string to find. Can be either a character or a binary data value
String_expression3 the string to replace. Can be either a character or a binary data value
Use: Select replace (' AB ', ' abcdef ', ' H ')

Date-time functions

GETDATE
Gets the date and time of the current SQL Server server
Use: Select GETDATE ()

getUTCDate
Gets the UTC time of the current SQL Server server
Use: Select getUTCDate ()

Year
Get year information for a date
Syntax structure: Year (date)
Parameter description: Date can be an expression of time, date, smalldatetime, DateTime, datetime2, or DateTimeOffset value
Use: Select year (' 2012 ')

MONTH
Get month information for a date
Syntax structure: MONTH (date)
Parameter description: Same year
Use: Select month (' 2012-05 ')

Day
Get the day information for a date
Syntax structure: day (date)
Parameter description: Same year
Use: Select Day (' 2012-05-20 ')

DATEPART
Returns the specified part of a specified datetime value
Grammatical structure: DATEPART (datepart,date)
Parameter description:
DatePart specifying datetime output format codes

Description	Take value
Return Annual Information	Year, YYYY, YY
Return month information	Month, MM, M
Return date information	Day, DD, D
Return Week information	Week, WK, WW
Returns the weekly information of the week	WeekDay, DW
Return Quarterly information	Quarter, QQ, Q
Returns information for the day of the year	DayOfYear, DY, Y
Return hour information	Hour, HH
Return minute information	Minute, MI, N
Returns the second message	Second, SS, S
Returns the millisecond information	Millsecond, MS

return value: Int type Date time format
Use: Select DATEPART (yy,getdate ())

Datename
Returns a string of the specified DateTime format, just like the DatePart function, except that the return value is a character type
Use: Select Datename (Yy,getdate ())

DATEADD
Add a date to a specified integer value to get a new datetime type of data
Grammatical structure: DATEADD (datepart,numerical,date)
Parameter description:
Numerical the value to be added must be an integer, or the integer if it is a decimal
Date is an expression of time, date, smalldatetime, DateTime, datetime2, or DateTimeOffset value, a column expression, a user-defined variable, or a string
DatePart Date Time output format code
Use: Select DATEADD (yyyy, ' 2012-05-06 ', ' 2013-05-06 ')

DATEDIFF
Subtract two dates in a specific time format to get a new datetime type of data
Grammatical structure: DATEDIFF (datepart,startdate,enddate)
Parameter description:
StartDate is an expression of time, date, smalldatetime, DateTime, datetime2, or DateTimeOffset value, a column expression, a user-defined variable, or a string
EndDate is an expression of time, date, smalldatetime, DateTime, datetime2, or DateTimeOffset value, a column expression, a user-defined variable, or a string
DatePart Date Time output format code
Use: Select DateDiff (yyyy, ' 2012-05-06 ', ' 2013-05-06 ')

Type conversion function

CAST and convert their functions are the same, but the syntax is different
CAST
Syntax structure: CAST (expression as Date_type)
Parameter description: expression to convert
Use: Select CAST (' 123 ' as int)

CONVERT
Syntax structure: CONVERt (data_type (length), Expression,style)
Parameter description:
Data_type (length) data type
The value to be converted by expression
Format of Style output
Use: Select CONVERT (int,123)

SQL Server Common functions