1. Internal functions
1. Internal total function
1) COUNT (*) Returns the number of rows
2) COUNT (DISTINCT COLNAME) returns the number of unique values in the specified column
3) SUM (COLNAME/EXPRESSION) returns the sum of the numerical values of the specified column or expression;
4) SUM (DISTINCT COLNAME) Returns the sum of the unique values in the specified column
5) AVG (COLNAME/EXPRESSION) returns the numerical average value in the specified column or expression
6) AVG (DISTINCT COLNAME) Returns the average value of the unique value in the specified column
7) MIN (COLNAME/EXPRESSION) returns the minimum value of the specified column or expression
8) MAX (COLNAME/EXPRESSION) returns the maximum value of the specified column or expression
2. Date and time functions
1) DAY (DATE/DATETIME EXPRESSION) returns the month date in the specified expression
2) MONTH (DATE/DATETIME EXPRESSION) Returns the month in the specified expression
3) YEAR (DATE/DATETIME EXPRESSION) returns the year in the specified expression
4) WEEKDAY (DATE/DATETIME EXPRESSION) Returns the week of the week in the specified expression
5) DATE (NOT DATE EXPRESSION) returns the date value represented by the specified expression
6) TODAY Returns the date value of the current date
7) CURRENT[FIRST TO LAST] Returns the date time value of the current date
8) COLNAME/EXPRESSION UNITS PRECISION Returns the specified number of units of the specified precision
9) MDY (MONTH, DAY, YEAR) Returns the date value that identifies the specified year, month, and day
10) DATETIME (DATE/DATETIME EXPRESSION) FIRST TO LAST Returns the date and time value represented by the expression
11) INTERVAL (DATE/DATETIME EXPRESSION) FIRST TO LAST Returns the time interval value represented by the expression
12) EXTEND (DATETIME EXPRESSION, [FIRST TO LAST]) Returns the adjusted date or date time value
The To_char function converts datetime and date values into character values.
The To_date function converts character values into values of type datetime. Example to_date("1978-10-07 10:00" ,"%Y-%m-%d %H:%M)
Example 1. Used in conjunction with UNITS, specify date or time unit (year, month, day, hour, minute, seond, fraction):
let tmp_date = today + 3 UNITS day
Example 2. let tmp_date = MDY(10,30,2002) -- 2002-10-30
Example 3. let tmp_date = today + interval(7) day to day --The current time plus 7 days;
Note: This function is similar to 1;
Example 4. EXTEND converts date or date time value
let tmp_inthour = extend(datetime1,hour to hour)
3. Algebraic functions
1) ABS(COLNAME/EXPRESSION): Take the absolute value
2) MOD (COLNAME/EXPRESSION, DIVISOR) Returns the modulus (remaintenance) after dividing by the divisor
3) POW (COLNAME/EXPRESSION, EXPONENT) Returns an exponent of a value
Example: let tmp_float = pow(2,3) --8.00000000
4) ROOT (COLNAME/EXPRESSION, [INDEX]) Returns the root value of the specified column or expression
5) SQRT (COLNAME/EXPRESSION) returns the square root value of the specified column or expression
6) ROUND (COLNAME/EXPRESSION, [FACTOR]) Returns the rounded value of the specified column or expression
7) TRUNC (COLNAME/EXPRESSION, [FACTOR]) Returns the truncated value of the specified column or expression
Note: FACTOR specifies the decimal digits in the above two, if not specified, it is 0; if it is a negative number, it is integrated to the left of the decimal point;
Note: ROUND is rounded 4 times on the specified bit; TRUNC is directly truncated on the specified bit;
let tmp_float = round(4.555,2) --4.56
let tmp_float = trunc(4.555,2) --4.55
4. Exponential and logarithmic functions
1) EXP (COLNAME/EXPRESSION) returns the exponential value of the specified column or expression
2) LOGN (COLNAME/EXPRESSION) returns the natural logarithmic value of the specified column or expression
3) LOG10 (COLNAME/EXPRESSION) returns the logarithmic value of the base digit 10 of the specified column or expression
5. Trigonometric function
1) COS (RADIAN EXPRESSION) returns the cosine value of the specified radian expression
2) SIN (RADIAN EXPRESSION) sine
3) TAN (RADIAN EXPRESSION) tangent
4) ACOS (RADIAN EXPRESSION) Reverse Cosine
5) ASIN (RADIAN EXPRESSION) Arctosine
6) ATAN (RADIAN EXPRESSION)
7) ATAN2 (X, Y) Returns the polar coordinate angle component of coordinates (X, Y)
6. Statistical functions
1) RANGE(COLNAME) Returns the difference between the maximum and minimum values of the specified column = MAX(COLNAME)-MIN(COLNAME)
2) VARIANCE (COLNAME) Returns the sample variance of the specified column;
3) STDEV (COLNAME) returns the standard deviation of the specified column;
7. Other functions
1) USER Returns the current user name
2) HEX (COLNAME/EXPRESSION) returns the hexadecimal value of the specified column or expression
3) LENGTH (COLNAME/EXPRESSION) returns the length of the specified character column or expression
4) TRIM (COLNAME/EXPRESSION) deletes the characters before and after the specified column or expression
5) COLNAME/EXPRESSION || COLNAME/EXPRESSION Returns the characters that are combined together;
8. Cardinality function:
1) The cardinality (IDS only) function counts the number of elements contained in the set.
Intelligent large object function, (only applicable to IDS)
filetoblob( ), copy the file into the BLOB column
filetoclob( ), copy the file into the CLOB column
locopy( ), copy data of type BLOB or CLOB to another BLOB or CLOB column
lotofile( ), copy BLOB or CLOB to file
9. String processing function:
lower, convert each uppercase letter in the string to lowercase letter
upper, convert each lowercase letter in the string to uppercase letter
initcap, convert the first letter of each word in the string to uppercase
replace, convert a set of characters in a string into other characters, such as replace(col,"each","eve")
substr, returns a part of the string, such as substr(col,1,2)
substring, returns a part of the string, for example substring(col,from 1 to 4)
lpad, a copy of the string that has been filled or truncated on the left using the lpad function that has been repeated to the necessary number of times, depending on the specified length of the fill part in the string.
For example: field col is of type char(15), select lpad(col,21,"_") from tab_name is displayed as adding six _ before col.
Rpad, using the rpad function to have a copy of the string filled or truncated on the right with a sequence of characters that have repeated the number of times, depending on the specified length of the fill part in the string.
For example: field col is of type char(15), select rpad(col,21,"_") from tab_name is displayed as adding six _ after col.
10. Other functions:
hex, returns the hexadecimal number of the expression
round, return the rounded value of the expression
trunc, return the trunc value of the expression
length, calculate the length of the expression
user, return the user name of the user who executed the query (login account name)
Today, return to the current system date
dbservername, return the name of the database server, same as sitename
dbinfo, return the database related information
decode, function to convert an expression with one value to another value
decode(test,a,a_value,b,b_value,c,c_value...), decode functions do not support TEXT and BYTE types.
Nvl, to convert the expression evaluated to empty to another value you want to specify.
In addition, you can also use stored procedures in select statements, such as select spl($test) from tab_name
2. IDS internal functions
1. DBSERVERNAME Returns the database server name let tmp_char=DBSERVERNAME
2. SITENAME Returns the database server name let tmp_char=SITENAME
Note: Both functions are the same;
3. DBINFO (‘SPECIAL_KEYWORD’) returns only keyword value
Example 1: Return the DBSPACE name of each table in the data
select dbinfo('dbspace',partnum),tabname from systables
where tabid>99 and tabtype='T' (OK)
Example 2: Return the last SERIAL value inserted in any table
select dbinfo('sqlca.sqlerrd1') from systables where tabid = 1
Example 3: Return the number of rows processed by the last SELECT, INSERT, UPDATE, DELETE or EXECUTE PROCEDURE statement;
select dbinfo('sqlca.sqlerrd2') from systables where tabid=1;
3. Others
There are 38 functions in this function library
Currently, the Informix versions supported are ODS, SE and Universal Server (IUS). In the ODS version, all data types are fully supported, while in IUS, SLOB and CLOB are not supported.
To install the Informix Client on the Web Server, before compiling PHP (before configure), you must first set up three environment variables: IFX_LIBDIR, IFX_LIBS and IFX_INCDIR. If this is the version, you still need to configure the HAVE_IFX_IUS environment variable. At the same time, the option --with_informix=yes should be added when configuring.
ifx_connect: Opens the Informix server connection.
ifx_pconnect: Opens the Informix server for continuous connection.
ifx_close: Close the Informix server connection.
ifx_query: Send a query string.
ifx_prepare: prepare query string.
ifx_do: executes prepared query string.
ifx_error: Gets the last error in Informix.
ifx_errormsg: Get the last error message of Informix.
ifx_affected_rows: Gets the number of columns affected by the last operation of Informix.
ifx_getsqlca: Get the sqlca information after query.
ifx_fetch_row: Returns each field in a single column.
ifx_htmltbl_result: Convert query return data into HTML table.
ifx_fieldtypes: Lists the SQL fields of Informix.
ifx_fieldproperties: Lists the SQL field properties of Informix.
ifx_num_fields: Gets the number of returned fields.
ifx_num_rows: Gets the number of returned columns.
ifx_free_result: Release return memory occupied.
ifx_create_char: Create a character class.
ifx_free_char: Delete character class.
ifx_update_char: Change character class.
ifx_get_char: Get the character class.
ifx_create_blob: Create a long-bit class.
ifx_copy_blob: Copy long bit class.
ifx_free_blob: Delete the long bit class.
ifx_get_blob: Get the long position class.
ifx_update_blob: Change the long bit class.
ifx_blobinfile_mode: Configure long bit class mode.
ifx_textasvarchar: Configure text mode default value.
ifx_byteasvarchar: Configure the default value of bit group mode.
ifx_nullformat: Configure the default value of empty character mode.
ifxus_create_slob: Creates the slob class.
ifx_free_slob: Delete the slob class.
ifxus_close_slob: Delete the slob class.
ifxus_open_slob: Open the slob class.
ifxus_tell_slob: Returns the current file or finds the location.
ifxus_seek_slob: Configure the current file or find the location.
ifxus_read_slob: Reads a specified number of slob classes.
ifxus_write_slob: Writes a string to the slob class.
Previous page12Read the full text