SQL2000 function Summary

I. character conversion functions
1. ASCII ()
Returns the ASCII value of the leftmost character of a character expression. In the ASCII () function, strings with numbers are not enclosed by '', But strings with other characters must be enclosed by''. Otherwise, an error occurs.
2. Char ()
Converts an ascii code to a character. If no value is entered ~ ASCII code value between 255, char () returns NULL.

3. Lower () and Upper ()
Lower () converts all strings to lowercase letters, and upper () converts all strings to uppercase letters.
4. STR ()
Converts numeric data to numeric data.
STR (<float_expression_r> [, length [, <decimal>])
Length indicates the length of the returned string, and decimal indicates the number of decimal places returned. If no length is specified, the default length value is 10 and the default value of decimal is 0.
If length or decimal is a negative value, null is returned;
When length is less than the number of digits to the left of the decimal point (including the symbol bit), return length *;
Obey length first, and then take decimal;
When the number of digits of the returned string is less than length, spaces are filled on the left.

Ii. Space-removing Functions
1. ltrim () removes spaces in the string header.

2. rtrim () removes spaces at the end of the string.

Iii. substring Functions
1. Left ()
Left (<character_expression_r>, <integer_expression_r>)
Returns the character character_expression_r starting from integer_expression_r.

2. Right ()
Right (<character_expression_r>, <integer_expression_r>)
Returns the character character_expression_r starting from integer_expression_r.

3. substring ()
Substring (<expression_r>, <starting _ Position>, length)
Returns the length of the string starting from the start _ Position Character on the left.

Iv. String comparison functions
1. charindex ()
Returns the starting position of a specified substring in a string.
Charindex (<'substring _ expression_r '>, <expression_r>)
Here, substring _ expression_r is the character expression to be searched for, and expression_r can be a string or a column name expression. If no substring is found, the return value is 0.
This function cannot be used for text and image data types.
2. patindex ()
Returns the starting position of a specified substring in a string.
Patindex (<'% substring _ expression_r %'>, <column _ name>) must have a percent sign (%) before and after the substring expression; otherwise, the return value is 0.
Unlike the charindex function, wildcards can be used in the child strings of the patindex function, which can be used for char, varchar, and text data types.

V. String operation functions
1. quotename ()
Returns a string enclosed by a specific character.
Quotename (<'character _ expression_r '> [, quote _ character]). Quote _ character indicates the character used to enclose the string. The default value is "[]".
2. Replicate ()
Returns a string that repeats the number specified by character_expression_r.
Replicate (character_expression_r integer_expression_r) if the value of integer_expression_r is negative, null is returned.

3. Reverse ()
Sorts the characters of a specified string in reverse order.
Reverse (<character_expression_r>) character_expression_r can be a string, a constant, or a column value.

4. Replace ()
Returns the string with the specified substring replaced.
Replace (<string_expression_r1>, <string_expression_r2>, and <string_expression_r3>) replace string_expression_r3 with the substring string_expression_r2 in string_expression_r1.

4. Space ()
Returns a blank string with a specified length.
Space (<integer_expression_r>) if the value of integer_expression_r is negative, null is returned.

5. Stuff ()
Replace the specified position and length of the string with another substring.
Stuff (<character_expression_r1>, <start _ Position>, <length>, <character_expression_r2>)
If the start position is negative or the length value is negative, or the start position is greater than the length of character_expression_r1, the return value is null.
If the length is greater than the start _ Position in character_expression_r1, character_expression_r1 retains only the first character.

Vi. Data type conversion functions
1. Cast ()
Cast (<expression_r> as <data _ type> [length])

2. Convert ()
Convert (<data _ type> [length], <expression_r> [, style])

1) data_type is the data type defined by the SQL server system. Custom Data Types cannot be used here.
2) length is used to specify the data length. The default value is 30.
3) convert the char or varchar type to an integer type such as Int or samllint. The result must be a value with a positive or negative number.
4) The conversion from text to Char or varchar is a maximum of 8000 characters, that is, the char or varchar data type is of the maximum length.
5) data stored in the image type is converted to the binary or varbinary type, up to 8000 characters.
6) convert the integer to the money or smallmoney type and process it according to the currency unit of the defined country, such as RMB, USD, and pound.
7) the bit type conversion converts a non-zero value to 1 and is still stored as a bit.
8) if you try to convert the data type to different lengths, the converted value will be truncated and "+" will be displayed after the converted value to identify the occurrence of this truncation.
9) use the style option of the convert () function to display the date and time in different formats. Style is the conversion style number provided by the SQL server system when converting datatime and smalldatetime to a string. Different style numbers have different output formats.

 

VII. Date Functions
1. Day (date_expression_r)
Returns the date value in date_expression_r.

2. Month (date_expression_r)
Returns the month value in date_expression_r.

3. Year (date_expression_r)
Returns the Year Value in date_expression_r.

4. dateadd ()
Dateadd (<datepart>, <number>, <date>)
Returns the new date generated by the specified date plus the specified additional date interval number. The value of "datepart" is as follows:
5. datediff ()
Datediff (<datepart>, <date1>, <date2>)
Returns the difference between two specified dates in datepart, that is, the difference between date2 and date1. The result value is an integer with positive and negative signs.

6. datename ()
Datename (<datepart>, <date>)
Returns the specified part of the date in the form of a string. Specified by datepart.

7. datepart ()
Datepart (<datepart>, <date>)
Returns the specified part of the date in the form of an integer. This part 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 ()
Returns the current date and time of the system in the default format of datetime.

ABS (number) gets the absolute value of the value.
ATN (number) gets the arc tangent value of an angle.
Callbyname (object, procname, usecalltype, [ARGs ()]) is used to execute the method, set, or return the attributes of an object.
The cbool (expression_r) Conversion expression is of the boolean type.
The cbyte (expression_r) Conversion expression is a byte type.
The cchar (expression_r) Conversion expression is in bytes state.
The cdate (expression_r) Conversion expression is of the date type.
The cdbl (expression_r) Conversion expression is of the double type.
The cdec (expression_r) Conversion expression is of the decimal type.
The CINT (expression_r) Conversion expression is of the integer type.
The clng (expression_r) Conversion expression is of the long type.
The cobj (expression_r) Conversion expression is of the object type.
The cshort (expression_r) Conversion expression is of the short type.
The csng (expression_r) Conversion expression is of the single type.
The CSTR (expression_r) Conversion expression is of the string type.
Choose (index, choice-1 [, choice-2,... [, choice-N]) selects the index value and returns the set parameter.
CHR (charcode) obtains the character content in ASCII code.
Close (filenumberlist) ends the archive enabled with open.
Cos (number) gets the cosine of an angle.
The type of the ctype (expression_r, typename) Conversion expression.
Dateadd (dateinterval, number, datetime) plus or minus the date or time.
Datediff (dateinterval, date1, date2) calculates the difference between two dates or times.
Datepart (dateinterval, date) is sent back to the year, month, day, or time based on the received date or time parameter.
Dateserial (year, month, day) combines the received parameters into a data of date type only with date.
Datevalue (datetime) obtains the date value that conforms to the country-specific style and contains the time.
Day (datetime) is returned to the date based on the received date parameter.
EOF (filenumber) returns true when it reaches the end of an opened file.
Exp (number) returns the power of e Based on the received parameter.
Filedatetime (pathname) returns the date and time when the file was created.
The length of the file returned by filelen (pathname) in bytes.
Filter (sourcearray, Match [, include [, compare]) searches for the specified string in the string array. If the array element contains the specified string, it is combined into a new string array and transmitted back. If you want to return an array element that does not contain the specified string, set the include parameter to false. The compare parameter is used to set whether the search is case sensitive. In this case, you only need to give textcompare constants or 1.
Fix (number) removes the fractional part of the parameter and returns the result.
Format (expression_r [, style [, firstdayofweek [, firstweekofyear]) converts the date, time, and value data into acceptable formats for each country.
Formatcurrency (expression_r [, numdigitsafterdecimal [, includeleadingdigit]) outputs the value as the amount type.
The numdigitsafterdecimal parameter indicates the number of decimal words, And the deleadingdigit parameter indicates whether to fill in the number of integer words when the integer is 0.
Formatdatetime (date [, namedformat]) returns formatted date or time data.
Formatnumber (expression_r [, numdigitsafterdecimal [, includeleadingdigit]) is returned for formatting
. The numdigitsafterdecimal parameter indicates the number of decimal words, And the deleadingdigit parameter indicates whether to fill in the number of integer words when the integer is 0.
Formatpercent (expression_r [, numdigitsafterdecimal [, includeleadingdigit]) returns numeric data in percent format. The numdigitsafterdecimal parameter indicates the number of decimal words, And the deleadingdigit parameter indicates whether to fill in the number of integer words when the integer is 0.
Getattr (filename) returns the attribute value of the file or directory.
Hex (number) converts a value to a hexadecimal value.
The hour field of the time returned by hour (time). The type is integer.
IIF (expression_r, truepart, falsepart) when the return value of the expression is true Program Otherwise, execute the falsepart field.
Instr ([start,] string1, string2) searches for the characters set by the string2 parameter to appear in the character strings. Start is used to search for the characters. string1 is the string to be searched, string2 is the character to be searched.
INT (number) returns an integer smaller than or equal to the maximum integer of the received parameter.
Isarray (varname) determines whether a variable is of the array type. If it is an array, true is returned. Otherwise, false is returned.
Isdate (expression_r) determines whether the expression content is of the datetime type. If so, true is returned, and false is returned.
Isdbnull (expression_r) determines whether the expression content is null. If it is null, true is returned. Otherwise, false is returned.
Isnumeric (expression_r) checks whether the expression content is in a numeric state. If yes, true is returned. Otherwise, false is returned.
Join (sourcearray [, delimiter]) merges string arrays into unique strings. The delimiter parameter is set to add new strings to each element.
Lcase (string) converts a string to a lowercase font.
Left (string, length) gets the characters with the length parameter set length starting from the left side of the string.
Len (string) gets the length of the string.
Log (number) gets the natural logarithm of the value.
Ltrim (string) removes the left blank part of the string.
Mid (string, start [, length]) retrieves the string of the length after the character set by the Strat parameter in the string. If the length parameter is not set, all the characters after the start are retrieved.
Minute (time) gets the time content of the part, type is integer.
Mkdir (PATH) creates a new directory.
Month (date) indicates the month of the date. The type is integer.
Monthname (month) is used to obtain the complete description of the month based on the received month value.
Now () gets the current date and time.
Oct (number) converts a value to an octal value.
Replace (expression_r, find, replace) converts the string specified by the find parameter in the string to the string specified by the replace parameter.
Right (string, length) is a character set by the length parameter, which is obtained from the right of the string.
Rmdir (PATH) removes an empty directory.
RND () is used to obtain decimal places between 0 and 1. If different values are to be obtained each time, randomize must be added before use.
Rtrim (string) removes the white space on the right of the string.
The second part of the time content obtained by second (time). The type is integer.
Sign (number) indicates whether the value is positive or negative. If the value is positive, 1 is returned. If the value is negative,-1 is returned. If the value is 0, 0 is returned.
Sin (number) returns the sine of an angle.
Space (number) gets the blank string set by the number parameter.
Split (expression_r [, delimiter]) Splits a string into a String Array Using the condition string set by the delimiter parameter.
SQRT (number) returns the square root of a number.
STR (number) converts a number into a string and returns it.
Strreverse (expression_r) returns the result after the string content is reversed.
Tan (number) returns the tangent of an angle.
Timeofday () gets the time that does not contain the date currently.
Timer () gets the number of seconds from 0: 00 to the current time. The type is double.
Timeserial (hour, minute, second) combines the received parameters into a data with only the time date type.
Timavalue (time) gets the time value that matches the country-specific style.
Today () gets the date that today does not contain the time.
Trim (string) removes the white space at the beginning and end of the string.
Typename (varname) gets the type of the variable or object.
Ubound (arrayname [, dimension]) obtains the final index value of the array. The dimension parameter is used to obtain the final index value of the dimension.
Ucase (string) converts a string to uppercase.
Val (string) converts a numeric string to a numeric State. If the string contains non-numeric content, it is removed and merged into a number.
In the weekday (date) parameter, the date is the day of the week, Sunday is 1, Monday is 2, and Tuesday is 3.
Weekdayname (number) gets the name of the week according to the received parameter. The parameters that can be received are 1 to 7, and the parameters that can be received are 1 on Sunday, 2 on Monday, and 3 on Tuesday.