Function used in the query
• In the query statement, not only the values listed in the table are queried and the internal functions can be used to query different types of information in the database. The internal function can be used in the SELECT list, where clause, and any allowed expressions. These functions are T-SQL extensions to SQL. The internal functions provided by Adaptive Server can be divided into the following categories:
l ???????? system function. Returns the information in the database;
l ???????? String function. Used to process strings or string expressions;
l ???????? Text function. Data for processing Text and Image type;
l ???????? Mathematical type. Functions used to handle triangles, geometries, and other data processing;
l ???????? Date function. Handle data for DateTime and SmallDateTime types;
l ???????? Data type conversion function (Convert). Used to convert a data type into another data type, or format the date into a variety of forms.
l ???????? Safety function. Returns information about secure services and user-defined roles
First, system functions
The system function returns specific information from the database, and most of them provide a simple way of querying system tables.
General grammar of system functions:
SELECT FUNCTION_NAME (Argument [S])
The system function can be used in the selection, where the WHERE clause, and any place to exist in the expression. For example: If you want to query "ZYF" user logo, enter:
SELECT? "User Label Number" = User_ID ("ZYF")
?
?
result:
User logo
-
3
In general, what kind of information will be returned in the function name.
User_name returns the user's name with the ID number as a parameter. As described below, if the ID number is 3 username
SELECT "User Name" = User_Name (3)
result:
username
-
ZYF
Find the name of the current user (that is, the username being used)
SELECT user_name ()
result:
username
-
DBO
Adaptive Server handles the user marker according to the following rules:
l ???????? When the system administrator becomes the owner of the database he use, its login user ID is assigned to 1;
L ???????? guest users' login user ID is always -1
l ???????? The user of the database owner is always DBO, his user ID is 1;
L ???????? guest user ID is 2
?
System function table
Function name
parameter
Return the result
COL_NAME
(Object_ID, Column_ID [, Database_ID])
Column name
COL_LENGTH
(Object_name, column_name)
The definition length of the column (using DATALENGTH is the length of the actual data)
CurunReServedpgs
(DBID, LSTART, UnreservedPGS)
The number of idle pages in the disk sheet. If the database is open, the value comes from memory; if the database is not open, the value comes from the system table SYSUSAGES list unreservedpgs
Data_PGS
(Object_ID, {DOAMPG | IOAMPG})
The number of pages used by table (DoAMPG) or index (iAMPG). This result does not include the number of pages used in internal results
Datalength
(Expression)
Returns the length of Expression. Expression can be a column name or a string constant. If the column name is returned to the length of the actual data in the column.
DB_ID
([Database_name])
The ID number of the database. The database name must be a character expression. If the database name is character constant, you must use quotation marks to cause it. Returns the ID number DB_NAME of the current database without giving a database name
([Database_ID])
data storage name. Database_id must be a numerical expression. If DATABASE_ID is not given, the current database name is returned.
Host_id
()
Current host name of customer process (non-Adaptive Server process)
Host_name
()
Index column name. Returns NULL if object_name is not a table name or view name.
INDEX_COL
(Object_name, index_id, key _ # [, user_id])
When Expression1 is NULL, use the value of Expression2 instead of the value of Expression1. The value of the expression must be implicitly converted, otherwise the Convert function must be used.
ISNULL
(Expression1, Expression2)
Manage the last machine of the log section? value. LastChance: Create a last chance value in the specified database; returns 1 when the last chance value of the specified database is exceeded, otherwise returns 0; unspend: Used to wake up the task hanging in the database and use it when the last chance value is exceeded This opportunity value is invalid; RESERVE: Returns the number of free logs required when you want to successfully dump a transaction log of specified size.
LCT_ADMIN
({{{"Lastchance" | "logfull" | "unspend"}
, Database_ID} |
"Reserve"
, LOG_PAGES})
Number of pages allocated to the list or index
RESERVED_PGS
(Object_ID, {DOAMPG | IOAMPG})
Estimate of the number of rows in the table
Rownt
(DOAMPG)
Database Object ID Number
Object_id
("ObjName")
Database object name
Object_name
(Obj_id)
Returns the ID number for the server from the table syslogins. If the value of the parameter server_user_name is not given, return the current user's server ID number.
SUSER_ID
([Server_User_name])
Returns the server username, the server user's ID number is stored in Table Syslogins, and if the value of the parameter server_user_id is not given, the current username is returned.
Tsequal
(TimeStamp, Timestamp2)
In the browsing mode, the value of timestamp is compared to prevent submission of those modified values. TimeStamp is the time sign of the browsed row; timestamp2 is the time flag of the storage line. This function allows users to use browse mode without calling DB_Library.
Used_pgs
(Object_ID, DOAMPG, IOAMPG)
The number of columns used by the table and cluster index
User
?
username
User_id
([User_name])
The user's ID number. Report the user ID number in the current database, if the parameter user_name is not given, return to the ID number of the current user.
User_name
([User_ID])
Returns the username corresponding to the user ID number of the current database. Returns the current username if the value of the parameter user_id is not provided.
VALID_NAME
(Character_EXPRESSION)
Returns 0 if character_expression is an invalid identifier (illegal characters or length exceeding 30), return 0; otherwise return non-0 values
VALID_USER
(Server_User_ID)
If the specified ID number has its corresponding username or alias in at least one of the Adaptive Server, returns 1; only the SA_ROLE or SSO_ROLE role can use the function.
?
example:
1. Find out the length of the title column in the table titles
SLELECT "Title Length" = Col_length ("Article", "Title")
Result:
? Title length
? ------
120
2, find out the length of the data in the Title column in the table Article
? Select length = datalength (title), Title
? from article
Result:
Length ??? Title
24 ??? ?????? soft revealing its Internet strategy
20 ????????????????? a launch two new network software
21 ???????????????????? IBM released a new version of SYSTEMVIEW
3. Find all the wages of all writers, if the salary is replaced with a NULL value to $ 10.00
?? SELECT ISNULL (Salary, $ 10.00)
?? from auths
result:
-
120.00
100.00
110.00
10.00
4, Isolated the user name of the user ID in System Table Sysusers
Select Name Form Sysusers
Where name = user_name (1)
result:
Name
-
DBO
?
