Shulou(Shulou.com)06/01 Report--

A complete Collection of SQL Server functions

The functions in SQL Server play a very prominent role in improving the efficiency of query. Being good at making good use of SQL function in query work or development work will not only be of great help to the work, but also greatly improve the efficiency of the work. But because there are so many kinds of SQL functions. Therefore, when in use, it is often impossible to fully understand their meaning and usage. The following will give a systematic introduction to the meaning and usage of the SQL function:

In practice, only SQL functions can be used in select statements to manipulate fields. Common functions can be divided into the following categories:

1. Character conversion function

1) ASCII ()

Returns the ASCII code value of the leftmost character of the character expression. In the ASCII () function, strings that are pure numbers can not be enclosed with'', but strings that contain other characters must be enclosed with'', otherwise an error will occur.

2) CHAR ()

Converts ASCII codes to characters. If you do not enter an ASCII code value between 0 and 255, CHAR () returns NULL.

3) LOWER () and UPPER ()

LOWER () converts all strings to lowercase; UPPER () converts all strings to uppercase.

4) STR ()

Convert numeric data to character data.

STR (, length [,]])

Length specifies the length of the returned string, and decimal specifies the number of decimal places returned. If no length is specified, the default value of length is 10 and the default value of decimal is 0.

Returns NULL when length or decimal is negative

Return length * when length is less than the number of digits to the left of the decimal point (including symbol bits)

Obey length first, then take decimal

When the number of string digits returned is less than length, fill in the blanks on the left.

2. Unblanking function

1) LTRIM () removes the spaces in the header of the string.

2) RTRIM () removes the space at the end of the string.

3. String comparison function

1) CHARINDEX ()

Returns the starting position of a specified substring in a string.

CHARINDEX (,)

Where substring _ expression is the character expression you are looking for, and expression can be a string or a column name expression. If no substring is found, a value of 0 is returned.

This function cannot be used with TEXT and IMAGE data types.

2) PATINDEX ()

Returns the beginning of the occurrence of a specified substring in a string.

PATINDEX (,) where the substring expression must have a percent sign "%" before and after the expression, otherwise the return value is 0.

Unlike the CHARINDEX function, wildcards can be used in substrings of the PATINDEX function, and this function can be used with CHAR, VARCHAR, and TEXT data types.

4. String operation function

1) QUOTENAME ()

Returns a string enclosed by a specific character.

QUOTENAME ([, quote_ character]) where quote_ character indicates the characters used in the enclosed string, and the default value is "[]".

2) REPLICATE ()

Returns a string that repeats character_expression a specified number of times.

REPLICATE (character_expression integer_expression) returns NULL if the integer_expression value is negative.

3) REVERSE ()

Reverses the character order of the specified string.

REVERSE () where character_expression can be a string, a constant, or the value of a column.

4) REPLACE ()

Returns a string that has replaced the specified substring.

REPLACE (,) replaces the substring string_expression2 in string_expression1 with string_expression3.

5) SPACE ()

Returns a blank string of specified length.

SPACE () returns NULL if the integer_expression value is negative.

6) STUFF ()

Replaces the substring of the specified position and length with another substring.

STUFF (,)

If the starting position is negative or the length value is negative, or if the starting position is greater than the length of character_expression1, the NULL value is returned.

If the length length is greater than the length to the right of the start_ position in the character_expression1, the character_expression1 retains only the first character.

5. Take the substring function

1) left ()

LEFT (,)

Returns integer_expression characters from the left of character_expression.

2) RIGHT ()

RIGHT (,)

Returns character_expression with integer_expression characters from the right.

6. Data type conversion function

1) CAST ()

CAST (AS [length])

2) CONVERT ()

CONVERT ([length], [, style])

A) data_type is a data type defined by the SQL Server system, and user-defined data types cannot be used here.

B) length is used to specify the length of the data, with a default value of 30.

C) convert the CHAR or VARCHAR type to an INTEGER type such as INT or SAMLLINT, and the result must be a positive or negative sign.

D) the conversion from TEXT type to CHAR or VARCHAR type is up to 8000 characters, that is, the CHAR or VARCHAR data type is the maximum length.

E) data stored by the IMAGE type is converted to the BINARY or VARBINARY type, with a maximum of 8000 characters.

F) convert the integer value to MONEY or SMALLMONEY type and deal with it according to the currency unit of the defined country, such as RMB, US dollar, pound sterling, etc.

G) the conversion of the BIT type converts the non-zero value to 1 and is still stored as the BIT type.

H) an attempt to convert to a data type of different length will truncate the conversion value and display "+" after the conversion value to indicate that this truncation has occurred.

I) date and time can be displayed in different formats with the style option of the CONVERT () function. Style is the conversion style number provided by the SQL Server system when converting DATATIME and SMALLDATETIME data into strings. Different style numbers have different output formats.

7. Date function

1) day (date_expression)

Returns the date value in date_expression

2) month (date_expression)

Returns the month value in date_expression

3) year (date_expression)

Returns the year value in date_expression

4) DATEADD ()

DATEADD (,)

Returns the new date generated by the specified date date plus the specified extra date interval number.

5) DATEDIFF ()

DATEDIFF (,)

Returns the difference in datepart between two specified dates, that is, the difference between date2 and date1, resulting in an integer value with a plus or minus sign.

6) DATENAME ()

DATENAME (,)

Returns the specified part of the date as a string. Specified by datepart.

7) DATEPART ()

DATEPART (,)

Returns the specified portion of the date as an integer value. This section is specified by datepart.

DATEPART (dd, date) is equivalent to DAY (date)

DATEPART (mm, date) is equivalent to MONTH (date)

DATEPART (yy, date) is equivalent to YEAR (date)

8) GETDATE ()

8. User-defined function

1) Scalar custom function

There is a definite return value

Format: Create function function_name [inputparameters]

Returns datatype

AS

Begin

Code

Return expression

End

Calling method: Select dbo. Function name

2) embedded table function

Format: Creae function function_name (inputparameters)

Returns table

AS

Return (select code)

Returns the current date and time of the system in the default format of DATETIME.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.