SQL Server basic functions (1)

1. isnull () function

Purpose: replace null with the specified replacement value.

Syntax:Isnull (check_expression, replacement_value)

Parameter: check_expression can be of any type.

Rereplacement_value returns the expression when check_expression is null. Replacement_value must be of the same type as check_expression.

Return type

Returns the same type as check_expression.

Note

If check_expression is not null, the value of this expression is returned; otherwise, the value of replacement_value is returned.

For example, the title, type, and price of all books in the table are displayed. If the price for a title is null, the price displayed in the result set is 0.00.

Select bookname, type, isnull (price, 0.00) as price

From table

 

2. datediff () function

Purpose: return the number of days between two dates.

 

Syntax:Datediff (datepart, startdate, enddate)

Parameter: datepart indicates the time interval (for example, YY, mm, DD, week, HH ......)

Startdate, enddate is a valid date expression

Return Value: The returned value corresponds to datepart.

Example:(1) returns the record of the current day

Select * from table where datediff (DD, datetime, getdate () = 0// Datetime is the column name in the table structure. The type is time.

(2) returns a weekly record.

Select * from table where datediff (Week, datetime, getdate () = 0

3. Convert () function

Explicitly converts a data type expression to another data type.

Syntax: Convert (type, expression) | convert (type, expression, Format)

Parameter: the data type that the type expression converts.

The expression to be converted.

Format: for the conversion process that converts a string to the date or time data type and the opposite, format is the style that describes the date format string to be used.Code.

Return type:

Returns the same type as type.

Example: (1) Select * from table where id = convert (varchar (2), substring ("abcdefgh", 1, 2 ))

(2) Select convert (varchar (10), datetime, 21)From table// Datetime is the column name in the table structure. The type is time. 21 is the value corresponding to the YY-mm-dd format.

4. substring () function

Returns part of a character, binary, text, or image expression.

 

Syntax: substring (expression, start, length)

 

Parameters:

Expression string, binary string, and text.

Start indicates the start position of the substring.

Length refers to the length of the sub-string. If it is null, the length is till the end of the string.

Return type: Same as expression.

 

For example, return the first letter of all strings in the name attribute column of the table.

Select substring (name, 1, 1) as first letter from table

 

5.Set ansi_nulls on and set quoted_identifier on
When set quoted_identifier is on, the identifiers can be separated by double quotation marks, and the text must be separated by single quotation marks. When set quoted_identifier is off, the identifier cannot be enclosed in quotation marks and must comply with all rules for the transact-SQL identifier. The SQL-92 standard requires that the value be false when a null value is equal to (=) or not equal to (<>.

 

When set ansi_nulls is on, even if column_name contains a null value, the SELECT statement using where column_name = NULL still returns zero rows. Even if column_name contains non-null values, the SELECT statement using where column_name <> null still returns zero rows.

when set ansi_nulls is off, equal to (=) and not equal to (<>) comparison operators do not comply with SQL-92 standards. Use the SELECT statement where column_name = NULL to return the rows containing null values in column_name. Use the where column_name <> null SELECT statement to return rows with non-null values in the column. In addition, use the SELECT statement where column_name <> xyz_value to return all rows that are neither xyz_value nor null.