Length and syntax analysis Datalength (char_expr) Returns the length of characters in char_expr, ignoring trailing null characters. Substring (expression, start, length) Returns some strings. Right (char_expr, int_expr) Returns the int_expr character on the Right of char_expr. Basic string operations Upper (char_expr) Converts char_expr to a large write form. Lower (char_expr) Convert char_expr to lowercase Space (int_expr) Generate a string with int_expr Spaces Replicate (char_expr, int_expr) Repeat char_expr, int_expr times Stuff (expr1, start, length, expr2) Use expr2 to replace the length of start in epxr1 Reverse (char_expr) Anti-write text in char_expr Ltrim (char_expr) Empty Delete Header Rtrim (char_expr) Delete tail null Format Conversion ASCII (char_expr) Returns the ASCII value of the first character in char_expr. Char (int_expr) Convert ASCII code to character STR (float_expr [, length [, decimal]) Convert numeric to numeric Soundex (char_expr) Returns the soundex value of char_expr. Difference (char_expr1, char_expr2) Returns the soundex Value Difference of the expression. Intra-string SEARCH Charindex (char_expr, expression) Returns the starting position of the specified char_expr; otherwise, it is 0. Patindex ("% pattern %", expression) Returns the starting position of the specified style. Otherwise, the value is 0. Datalength is used to determine the length of a variable string. Soundex is used to determine whether a string has similar pronunciation. Difference returns the value 0-4. 0 indicates the least similarity, and 4 indicates the most similar. Wildcard % Matches any number of characters or no characters _ Match any single character (space placeholder) [] Specifying a valid range or a "or" Condition [ABG] A, B, G [A-C] a, B, c [A-CE-G] A, B, C, E, F, G [^ ABG] Except for A, B, G [^ A-C] Except for a, B, c Escape Clause You can use an escape character to include wildcards as text when searching strings. ANSI-89 SQL standard defines an escape clause to specify an escape character By default, [] is used to escape a wildcard. For example: Select * From test_tab Where description like "% 20 [%] %" Syntax: Like char_expression escape escape_character Example Select * From test_tab Where description like "% 20 # %" escape "#" + String-connected characters Select au_laname + "," + au_fname from authors Mathematical functions ABS (numeric_expr) Returns the absolute value of a specified value. Ceiling (numeric_expr) Returns the smallest integer greater than or equal to the specified value. Exp (float_expr) Returns the specified exponential value. Floor (numeric_expr) Returns the maximum integer that is less than or equal to the specified value. Pi () Returns a constant of 3.1415926 Power (numeric_expr, power) Returns the power of numeric_expr. Rand ([int_expr]) Returns a random floating point number between 0 and 1. You can specify the base value. Round (numeric_expr, int_expr) Round the value expression to the precision specified by int_expr. Sign (int_expr) Returns positive + 1, zero 0, or negative-1. SQRT (float_expr) Returns the square root of a specified value. SQL Server supports all standard trigonometric functions and other useful functions Date Functions Getdate () Returns the current system date and time. Datename (datepart, date_expr) Returns the value of the specified part of date_expr in string format and converts it to a proper name. Datepart (datepart, date_expr) Returns the specified part of the date_expr value as an integer. Datediff (datepart, date_expr1, date_expr2) Returns the date_expr2-date_expr1 through the specified datepart Measurement Dateadd (datepart, number, date_expr) Return date, generated by adding the date part of the specified number on date_expr Datepart Date part abbreviation value range YY 1753-9999 Quarterly QQ 1-4 Mm 1-12 Day 1-366 every year DD 1-31 days Week wk 1-54 DW 1-7 (1 = Sunday) on Sunday) Hour HH 0-23 Min mi 0-59 SS 0-59 seconds MS in milliseconds 0-999 Example: Select invoice_no, Datediff (DD, date_shipped, getdate ()) From invoices Where balance_due> 0 Conversion Function convert This function changes the value from one type to another. Convert (datetype [(length)], expression) Select "advance =" + convert (char (12), advance) From titles Date Conversion Convert (datetype [(length)], expression, Format) Format specifies the format for converting a date. The value is as follows: No century, Century, conversion string, Date Format 0 or 100 mon dd YYY hh: miam (or pm) 1 101/DD/yy 2 102 yy. Mm. dd 3 103 dd/mm/yy 4 104 dd. mm. yy 5 105 DD-mm-yy 6 106 dd mon YY 7 107 mon DD, YY 8 108 hh: mm: SS 9 or 109 mon DD, yyyy hh: MI: SS: mmmam (or pm) 10 110mm-dd-yy 11 111 YY/MM/dd 12 112 yymmdd System Functions Function Definition Access and security information Host_id () current host process ID of the customer Process Host_name () Name of the current master computer of the customer Process Suser_id (["login_name"]) User's SQL Server ID Suser_name ([server_user_id]) User's SQL Server login name User_id (["name_in_db"]) ID of the user in the database User_name ([user_id]) name of the user in the database User name in the database Current active role of the show_role () User Database and Object Information Db_id (["db_name"]) Database ID Db_name ([db_id]) Database Name Object_id ("objname") ID of the database object Object_name (obj_id]) database object number Column name of the col_name (obj_id, col_id) object Col_length ("objname", "colname ") Index_col ("objname", index_id, key #) Name of the indexed Column Valid_name (char_expr) If char_expr is not a valid identifier, 0 is returned. Data Functions Datalength (expression) returns the length of expression in bytes Tsequal (timestamp1, timestamp2) compares the timestamp value. If the timestamp value does not match, an error message is returned. Isnull () The isnull function replaces the null value in the query column or total with the specified value. Example: Select AVG (isnull (total_order, $0 )) From invoices |