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 |
