Learning focus
-
According to the purpose, functions can be roughly divided into arithmetic functions, string functions, date functions, conversion functions and aggregate functions.
-
There are many kinds of functions. You don't need to remember them all. Just remember the representative functions. Others can be queried when you use them.
1, Types of functions
In the previous articles, I learned the syntax structure of SQL and other rules that must be observed. This article will make a little change to learn some convenient tools that come with SQL - functions.
We are Aggregate query on table I've learned the concept of function in. Let's review it here. The so-called function is the function of inputting a value to obtain the corresponding output result. The input value is called parameter and the output value is called return value.
KEYWORD
-
function
-
parameter
-
Return value
Functions can be roughly divided into the following types.
-
Arithmetic function (function used for numerical calculation)
-
String function (function used for string operation)
-
Date function (function used for date operation)
-
Conversion function (function used to convert data types and values)
-
Aggregate function (function used for data aggregation)
KEYWORD
-
Arithmetic function
-
String function
-
Date function
-
Conversion function
-
Aggregate function
We are already Aggregate query on table Learned the relevant content of aggregate function in. You should have a preliminary understanding of function. Aggregate functions basically only include COUNT, SUM, AVG, MAX and MIN, while the total number of other functions exceeds 200. You may think how there are so many functions, but you don't need to worry. Although there are a large number, there are only 30 ~ 50 commonly used functions. Unfamiliar functions can be understood by referring to the reference document (Dictionary) [1].
In this section, we will learn some representative functions. You don't need to remember all at once. You just need to know that there is such a function. You can refer to the reference documents for practical application.
Next, let's take a closer look at these functions.
2, Arithmetic function
Arithmetic function is the most basic function. In fact, we have studied it before, and some readers may have remembered it. Yes, it is Arithmetic and comparison operators Introduce the four operations of addition, subtraction, multiplication and division.
KEYWORD
- Arithmetic function
-
+(addition)
-
-(subtraction)
-
*(multiplication)
-
/(Division)
KEYWORD
-
+Operator
-
-Operator
-
*Operator
-
/Operator
Because these arithmetic operators have the function of "returning corresponding output results according to input values", they are excellent arithmetic functions. Here we will introduce other representative functions.
To learn arithmetic functions, we first create a sample math table according to Listing 1.
NUMERIC is a data type supported by most DBMS. It specifies the size of the value in the form of NUMERIC (full digits, decimal digits). Next, we will introduce the common arithmetic function - the ROUND function. Since the ROUND function in PostgreSQL can only use NUMERIC data, we also use this data type in the example.
Listing 1 creates the SampleMath table
-- DDL : Create table CREATE TABLE SampleMath (m NUMERIC (10,3), n INTEGER, p INTEGER);
SQL Server PostgreSQL
-- DML : insert data BEGIN TRANSACTION; -----① INSERT INTO SampleMath(m, n, p) VALUES (500, 0, NULL); INSERT INTO SampleMath(m, n, p) VALUES (-180, 0, NULL); INSERT INTO SampleMath(m, n, p) VALUES (NULL, NULL, NULL); INSERT INTO SampleMath(m, n, p) VALUES (NULL, 7, 3); INSERT INTO SampleMath(m, n, p) VALUES (NULL, 5, 2); INSERT INTO SampleMath(m, n, p) VALUES (NULL, 4, NULL); INSERT INTO SampleMath(m, n, p) VALUES (8, NULL, 3); INSERT INTO SampleMath(m, n, p) VALUES (2.27, 1, NULL); INSERT INTO SampleMath(m, n, p) VALUES (5.555,2, NULL); INSERT INTO SampleMath(m, n, p) VALUES (NULL, 1, NULL); INSERT INTO SampleMath(m, n, p) VALUES (8.76, NULL, NULL); COMMIT;
Specific SQL
Different DBMS transactions have different syntax. When the DML statement in code listing 1 is executed in MySQL, the ① part needs to be changed to "start transaction;" and when it is executed in Oracle and DB2, the ① part is not needed (please delete it).
Please refer to the details affair Create transaction in.
Let's confirm the contents of the created table, which should contain columns m, n and p.
SELECT * FROM SampleMath;
results of enforcement
m | n | p ---------+---+-- 500.000 | 0 | -180.000 | 0 | | | | 7 | 3 | 5 | 2 | 4 | 8.000 | | 3 2.270 | 1 | 5.555 | 2 | | 1 | 8.760 | |
2.1 ABS - absolute value
Syntax 1 ABS function
ABS(numerical value)
ABS is a function of absolute values. Absolute value does not consider the sign of the value and represents the distance from a number to the origin. In short, the calculation method of absolute value is: the absolute value of 0 and positive number is itself, and the absolute value of negative number is the result after removing the symbol.
KEYWORD
-
ABS function
-
absolute value
Listing 2 calculates the absolute value of the value
SELECT m, ABS(m) AS abs_col FROM SampleMath;
results of enforcement
ABS on the right_ Col column is the absolute value of m column calculated by ABS function. Please note that the absolute value of - 180 is the result 180 after removing the symbol.
From the above results, we can find that when the parameter of ABS function is NULL, the result is also NULL. This is not the only ABS function. In fact, most functions return NULL for NULL [2].
2.2 MOD -- remainder
Syntax 2 MOD function
MOD(Divisor)
MOD is a function for calculating the division remainder (remainder), which is the abbreviation of modulo. For example, the remainder of 7 / 3 is 1, so the result of MOD(7, 3) is also 1 (code listing 3). Because there is no concept of remainder in decimal calculation, MOD function can only be used for columns of integer type.
KEYWORD
- MOD function
Listing 3 calculates the remainder of the division (n ÷ p)
Oracle DB2 PostgreSQL MySQL
SELECT n, p, MOD(n, p) AS mod_col FROM SampleMath;
results of enforcement
n | p | mod_col ---+---+-------- 0 | | 0 | | | | 7 | 3 | 1 5 | 2 | 1 4 | | | 3 | 1 | | 2 | | 1 | | | |
Usual
mod_col: return value of MOD(n,p) (remainder of n ÷ p)
Here is a point to note: mainstream DBMS supports MOD function, which is not supported by SQL Server.
Specific SQL
SQL Server uses the special operator (function) "%" to calculate the remainder. The same result as that in code listing 3 can be obtained by using the following special syntax. Readers who need to use SQL Server need special attention.
KEYWORD
- %Operator (SQL Server)
SQL Server
SELECT n, p, n % p AS mod_col FROM SampleMath;
2.3 ROUND - round
Syntax 3 ROUND function
ROUND(Object value, with the number of decimal places reserved)
The round function is used for rounding. Rounding is called round in English. If the number of digits specified for rounding is 1, the second decimal place will be rounded. If you specify 2 digits, the third digit is rounded (Listing 4).
KEYWORD
- ROUND function
Code listing 4 rounds the values in column m to the number of digits in column n
SELECT m, n, ROUND(m, n) AS round_col FROM SampleMath;
results of enforcement
m | n | round_col ---------+---+---------- 500.000 | 0 | 500 -180.000 | 0 | -180 | | | 7 | | 5 | | 4 | 8.000 | | 2.270 | 1 | 2.3 5.555 | 2 | 5.56 | 1 | 8.760 | |
Usual
m: Object value
n: Rounding digits
round_col: return value of ROUND(m,n) (rounded result)
3, String function
Up to now, the functions we have introduced are mainly arithmetic functions for numerical values, but in fact, arithmetic functions are only part of the functions provided by SQL (as is usually the case in other programming languages). Although arithmetic functions are often used, string functions are also often used.
KEYWORD
- String function
In daily life, we often replace, intercept and simplify strings like using numbers. Therefore, SQL also provides us with many functions to operate strings.
To learn about string functions, let's create a table (SampleStr), as shown in Listing 5.
Listing 5 creates the SampleStr table
-- DDL : Create table CREATE TABLE SampleStr (str1 VARCHAR(40), str2 VARCHAR(40), str3 VARCHAR(40);)
SQL Server PostgreSQL
-- DML : insert data BEGIN TRANSACTION; -------------① INSERT INTO SampleStr (str1, str2, str3) VALUES ('opx' ,'rt',NULL); INSERT INTO SampleStr (str1, str2, str3) VALUES ('abc' ,'def' ,NULL); INSERT INTO SampleStr (str1, str2, str3) VALUES ('Yamada' ,'Taro' ,'it's me'); INSERT INTO SampleStr (str1, str2, str3) VALUES ('aaa' ,NULL ,NULL); INSERT INTO SampleStr (str1, str2, str3) VALUES (NULL ,'xyz',NULL); INSERT INTO SampleStr (str1, str2, str3) VALUES ('@!#$%' ,NULL ,NULL); INSERT INTO SampleStr (str1, str2, str3) VALUES ('ABC' ,NULL ,NULL); INSERT INTO SampleStr (str1, str2, str3) VALUES ('aBC' ,NULL ,NULL); INSERT INTO SampleStr (str1, str2, str3) VALUES ('abc Taro' ,'abc' ,'ABC'); INSERT INTO SampleStr (str1, str2, str3) VALUES ('abcdefabc' ,'abc' ,'ABC'); INSERT INTO SampleStr (str1, str2, str3) VALUES ('micmic' ,'i' ,'I'); COMMIT;
Specific SQL
Different DBMS transactions have different syntax. When the DML statement in Listing 5 is executed in MySQL, you need to change part ① to "START TRANSACTION;". When executing in Oracle and DB2, the part that does not need ① (please delete).
Please refer to the details affair Section.
Let's confirm the contents of the created table, which should contain three columns: str1, str2 and str3.
SELECT * FROM SampleStr;
results of enforcement
str1 | str2 | str3 -----------+------+----- opx | rt | abc | def | Yamada | Taro | it's me aaa | | | xyz | @!#$% | | ABC | | aBC | | abc Taro | abc | ABC abcdefabc | abc | ABC micmic | i | I
3.1 | - splicing
Syntax 4 | function
String 1||String 2
In actual business, we often encounter abc + de = abcde, which wants to splice strings. In SQL, it can be realized by the "|" function transformed from two parallel vertical lines (code listing 6).
KEYWORD
- ||Functions
Listing 6 splices two strings (str1+str2)
Oracle DB2 PostgreSQL
SELECT str1, str2, str1 || str2 AS str_concat FROM SampleStr;
results of enforcement
str1 | str2 | str_concat -----------+------+------------ opx | rt | opxrt abc | def | abcdef Yamada | Taro | Taro Yamada aaa | | | xyz | @!#$% | | ABC | | aBC | | abc Taro | abc | abc Taro abc abcdefabc | abc | abcdefabcabc micmic | i | micmaci
Usual
str_concat: return value of STR1 | STR2 (splicing result)
When string splicing, if it contains NULL, the result is also NULL. This is because "||" is also a deformed function. Of course, more than three strings can also be spliced (code listing 7).
Listing 7 splices three strings (str1+str2+str3)
Oracle DB2 PostgreSQL
SELECT str1, str2, str3, str1 || str2 || str3 AS str_concat FROM SampleStr WHERE str1 = 'Yamada';
results of enforcement
str1 | str2 | str3 | str_concat ------+------+------+----------- Yamada | Taro | it's me | Taro Yamada, it's me
Usual
str_concat: return value of STR1 | STR2 | str3 (splicing result)
There is also a point to note here that the | function cannot be used in SQL Server and MySQL.
Specific SQL
SQL Server uses the "+" operator (function) to connect strings [3] . MySQL uses the CONCAT function to splice strings. Using the following SQL Server/MySQL specific syntax, you can get the same result as listing 7. In addition, the CONCAT function can also be used in SQL Server 2012 and later versions.
KEYWORD
-
+Operator (SQL Server)
-
CONCAT function (MySQL)
SQL Server
SELECT str1, str2, str3, str1 + str2 + str3 AS str_concat FROM SampleStr;
MySQL SQL Server 2012 and later
SELECT str1, str2, str3, CONCAT(str1, str2, str3) AS str_concat FROM SampleStr;
3.2 LENGTH -- string length
Syntax 5 LENGTH function
LENGTH(character string)
When you want to know how many characters a string contains, you can use the LENGTH function (Listing 8).
KEYWORD
- LENGTH function
Listing 8 calculates the length of the string
Oracle DB2 PostgreSQL MySQL
SELECT str1, LENGTH(str1) AS len_str FROM SampleStr;
results of enforcement
str1 | len_str -----------+-------- opx | 3 abc | 3 Yamada | 2 aaa | 3 | @!#$% | 5 ABC | 3 aBC | 3 abc Taro | 5 abcdefabc | 9 micmic | 6
Usual
len_str: return value of LENGTH(str1) (character length of str1)
It should be noted that this function cannot be used in SQL Server.
Specific SQL
SQL Server uses the LEN function to calculate the length of the string. Using the following SQL Server specific syntax, you can get the same result as listing 8.
KEYWORD
- LEN function (SQL Server)
SQL Server
SELECT str1, LEN(str1) AS len_str FROM SampleStr;
I think we should gradually understand the meaning of the sentence "there are many specific uses in SQL".
special column
Using the LENGTH function for one character may result in more than 2 bytes
In the LENGTH function, we should pay special attention to what unit the function uses to calculate the LENGTH of the string. This part is the content that will be learned in the above primary stages. Let's briefly introduce it here.
Some readers may already know that unlike half width English letters occupying 1 byte, full width characters such as Chinese characters will occupy more than 2 bytes (called multi byte characters). Therefore, when using a byte based function such as LENGTH in MySQL, the return result of "LENGTH (Yamada)" is 4. The same is the LENGTH function, and the execution results of different DBMS are also different [4].
KEYWORD
-
byte
-
multiple-byte character
Byte is the basic unit used to express the size of data in a computer. Usually "1 character = 1 byte". The unit byte (KB) is 1024 times the byte, the unit megabyte (MB) is 1024 times the kilobyte, and the unit Gigabyte (GB) is 1024 times the megabyte. It refers to "100 GB" and "250 GB" that are often used when the hard disk capacity is used, of which 100 GB refers to 1024 GB can be stored × one thousand and twenty-four × one thousand and twenty-four × 100 = 107374182400 half width English letters.
-
LENGTH function (MySQL)
-
CHAR_LENGTH function (MySQL)
Although there is some confusion, this is what I hope you can remember.
3.3 LOWER -- lowercase conversion
Syntax 6 LOWER function
LOWER(character string)
The LOWER function can only be used for English letters. It converts all strings in the parameter to lowercase (code listing 9). Therefore, this function is not suitable for occasions other than English letters. In addition, the function does not affect characters that are originally lowercase.
KEYWORD
- LOWER function
Listing 9 converts uppercase to lowercase
SELECT str1, LOWER(str1) AS low_str FROM SampleStr WHERE str1 IN ('ABC', 'aBC', 'abc', 'Yamada');
results of enforcement
str1 | low_str ------+-------- abc | abc Yamada | Yamada ABC | abc aBC | abc
Usual
low_str: return value of LOWER(str1)
Since there is a lower case conversion function, there must also be an UPPER case conversion function. UPPER is an UPPER case conversion function.
3.4 REPLACE - string replacement
Syntax 7 REPLACE function
REPLACE(Object string, string before replacement, string after replacement)
Using the REPLACE function, you can REPLACE part of the string with another string (listing 10).
KEYWORD
- REPLACE function
Listing 10 replaces part of the string
SELECT str1, str2, str3, REPLACE(str1, str2, str3) AS rep_str FROM SampleStr;
results of enforcement
str1 | str2 | str3 | rep_str -----------+------+------+--------- opx | rt | | abc | def | | Yamada | Taro | it's me | Yamada aaa | | | | xyz | | @!#$% | | | ABC | | | aBC | | | abc Taro | abc | ABC | ABC Taro abcdefabc | abc | ABC | ABCdefABC micmic | i | I | mIcmIc
Usual
str1: object string
str2: string before replacement
str3: replaced string
rep_str: return value of REPLACE(str1,str2,str3) (replacement result)
3.5 SUBSTRING -- interception of string
Syntax 8 SUBSTRING function (PostgreSQL/MySQL specific syntax)
SUBSTRING(Object string FROM Intercept start position FOR (number of characters intercepted)
You can use the SUBSTRING function to truncate a portion of the string (listing 11). The starting position of the interception is calculated from the leftmost side of the string [5].
KEYWORD
- SUBSTRING function
Listing 11 intercepts the characters in the 3rd and 4th bits of the string
PostgreSQL MySQL
SELECT str1, SUBSTRING(str1 FROM 3 FOR 2) AS sub_str FROM SampleStr;
results of enforcement
str1 | sub_str -----------+-------- opx | x abc | c Yamada | aaa | a | @!#$% | #$ ABC | C aBC | C abc Taro | c too abcdefabc | cd micmic | cm
Usual
sub_str: return value of SUBSTRING(str1 FROM 3 FOR 2)
Although the syntax of the SUBSTRING function above is a formal syntax recognized by standard SQL, only PostgreSQL and MySQL support this syntax.
Specific SQL
SQL Server simplifies the content in syntax 8a (syntax 8b).
Syntax 8a SUBSTRING function (SQL Server specific syntax)
SUBSTRING(Object string, starting position of interception, number of characters intercepted)
Oracle and DB2 further simplified the syntax and got the following results.
Syntax 8b SUBSTR function (Oracle/DB2 specific syntax)
SUBSTR(Object string, starting position of interception, number of characters intercepted)
SQL has so many specific syntax, which is really a headache. The special syntax in each DBMS to get the same result as listing 11 is shown below.
SQL Server
SELECT str1, SUBSTRING(str1, 3, 2) AS sub_str FROM SampleStr;
Oracle DB2
SELECT str1, SUBSTR(str1, 3, 2) AS sub_str FROM SampleStr;
3.6 UPPER - upper case conversion
Syntax 9 UPPER function
UPPER(character string)
The UPPER function can only be used for English letters, and it converts all strings in the parameter to uppercase (code listing 12). Therefore, this function is not applicable to situations other than English letters. In addition, the function does not affect characters that are originally uppercase.
KEYWORD
- UPPER function
Listing 12 converts lowercase to uppercase
SELECT str1, UPPER(str1) AS up_str FROM SampleStr WHERE str1 IN ('ABC', 'aBC', 'abc', 'Yamada');
results of enforcement
str1 | up_str ------+-------- abc | ABC Yamada | Yamada ABC | ABC aBC | ABC
Usual
up_str: return value of UPPER(str1)
In contrast, the LOWER function converts lowercase.
4, Date function
Although there are many date functions in SQL, most of them depend on their own DBMS, so they cannot be described uniformly [6] . This section will introduce the functions recognized by standard SQL that can be applied to most DBMS.
KEYWORD
- Date function
4.1 CURRENT_DATE - current date
Syntax 10 CURRENT_DATE function
CURRENT_DATE
CURRENT_ The date function can return the date of SQL execution, that is, the date when the function is executed. Since there are no parameters, parentheses are not required.
KEYWORD
- CURRENT_DATE function
Execution date is different, current_ The return value of the date function is also different. If you execute this function on December 13, 2009, you will get the return value "2009-12-13". If executed on January 1, 2010, you will get the return value "2010-01-01" (code listing 13).
Listing 13 gets the current date
SELECT CURRENT_DATE;
results of enforcement
date ------------ 2016-05-20
The function cannot be executed in SQL Server. In addition, the syntax in Oracle and DB2 is slightly different.
Specific SQL
SQL Server uses the following current_ The timestamp (described later) function to obtain the current date.
SQL Server
-- use CAST(The (described later) function will CURRENT_TIMESTAMP Convert to date type SELECT CAST(CURRENT_TIMESTAMP AS DATE) AS CUR_DATE;
results of enforcement
CUR_DATE ---------- 2010-05-25
When using this function in Oracle, you need to specify the temporary table (DUAL) in the FROM clause. When used in DB2, you need to add a half width space between cruent and DATE, and you also need to specify the temporary table SYSIBM.SYSDUMMY1 (equivalent to DUAL in Oracle). Please pay more attention to these confusing places.
Oracle
SELECT CURRENT_DATE FROM dual;
DB2
SELECT CURRENT DATE FROM SYSIBM.SYSDUMMY1;
4.2 CURRENT_TIME - current time
Syntax 11 CURRENT_TIME * * function**
CURRENT_TIME
CURRENT_ The time function can obtain the time of SQL execution, that is, the time when the function is executed (code listing 14). Since the function also has no arguments, parentheses are also unnecessary.
KEYWORD
- CURRENT_TIME function
Code listing 14 gets the current time
PostgreSQL MySQL
SELECT CURRENT_TIME;
results of enforcement
timetz ----------------- 17:26:50.995+09
This function also cannot be executed in SQL Server, and the syntax is also slightly different in Oracle and DB2.
Specific SQL
SQL Server uses the following current_ The timestamp function (described later) to obtain the current date.
-- use CAST The function (described later) will CURRENT_TIMESTAMP Convert to time type SELECT CAST(CURRENT_TIMESTAMP AS TIME) AS CUR_TIME;
results of enforcement
CUR_TIME ---------------- 21:33:59.3400000
The syntax used in Oracle and DB2 is as follows. Points needing attention and current_ Same as the date function. When used in Oracle, the results also include dates.
Oracle
-- Specify temporary table( DUAL) SELECT CURRENT_TIMESTAMP FROM dual;
DB2
/* CURRENT A half width space is used between and TIME to specify the temporary table SYSIBM.SYSDUMMY1 */ SELECT CURRENT TIME FROM SYSIBM.SYSDUMMY1;
4.3 CURRENT_TIMESTAMP - current date and time
Syntax 12 CURRENT_TIMESTAMP function
CURRENT_TIMESTAMP
The CURRENT_TIMESTAMP function has the function of CURRENT_DATE + CURRENT_TIME. Using this function, you can get the current date and time at the same time. Of course, you can also intercept the date or time from the result.
KEYWORD
- CURRENT_TIMESTAMP function
Code listing 15 gets the current date and time
SQL Server PostgreSQL MySQL
SELECT CURRENT_TIMESTAMP;
results of enforcement
now --------------------------- 2016-04-25 18:31:03.704+09
This function can be used in major DBMS such as SQL Server [7] However, like the previous CURRENT_DATE and CURRENT_TIME, the syntax of this function is slightly different in Oracle and DB2.
Specific SQL
Oracle and DB2 can get the same result as listing 15 by using the following writing method. The points needing attention are exactly the same as those in CURRENT_DATE.
Oracle
-- Specify temporary table( DUAL) SELECT CURRENT_TIMESTAMP FROM dual;
DB2
/* CURRENT A half width space is used between and TIME to specify the temporary table SYSIBM.SYSDUMMY1 */ SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1;
4.4 EXTRACT - intercept date element
Syntax 13 EXTRACT function
EXTRACT(Date element FROM date)
Use the EXTRACT function to EXTRACT a part of the date data, such as "year", "month", or "hour", "second", etc. (code listing 16). The return value of this function is not a date type, but a numeric type.
KEYWORD
- EXTRACT function
Code listing 16 intercepts the date element
PostgreSQL MySQL
SELECT CURRENT_TIMESTAMP, EXTRACT(YEAR FROM CURRENT_TIMESTAMP) AS year, EXTRACT(MONTH FROM CURRENT_TIMESTAMP) AS month, EXTRACT(DAY FROM CURRENT_TIMESTAMP) AS day, EXTRACT(HOUR FROM CURRENT_TIMESTAMP) AS hour, EXTRACT(MINUTE FROM CURRENT_TIMESTAMP) AS minute, EXTRACT(SECOND FROM CURRENT_TIMESTAMP) AS second;
results of enforcement
now | year | month | day | hour | minute | second ---------------------------+------+-------+-----+------+--------+------- 2010-04-25 19:07:33.987+09 | 2010 | 4 | 25 | 19 | 7 | 33.987
Note that SQL Server cannot use this function.
Specific SQL
SQL Server uses the following DATEPART function to get the same result as listing 16.
KEYWORD
- DATEPART function (SQL Server)
SQL Server
SELECT CURRENT_TIMESTAMP, DATEPART(YEAR , CURRENT_TIMESTAMP) AS year, DATEPART(MONTH , CURRENT_TIMESTAMP) AS month, DATEPART(DAY , CURRENT_TIMESTAMP) AS day, DATEPART(HOUR , CURRENT_TIMESTAMP) AS hour, DATEPART(MINUTE , CURRENT_TIMESTAMP) AS minute, DATEPART(SECOND , CURRENT_TIMESTAMP) AS second;
If Oracle and DB2 want to get the same result, the following changes need to be made. The precautions are exactly the same as those in CURRENT_DATE.
Oracle
-- stay FROM Clause to specify a temporary table( DUAL) SELECT CURRENT_TIMESTAMP, EXTRACT(YEAR FROM CURRENT_TIMESTAMP) AS year, EXTRACT(MONTH FROM CURRENT_TIMESTAMP) AS month, EXTRACT(DAY FROM CURRENT_TIMESTAMP) AS day, EXTRACT(HOUR FROM CURRENT_TIMESTAMP) AS hour, EXTRACT(MINUTE FROM CURRENT_TIMESTAMP) AS minute, EXTRACT(SECOND FROM CURRENT_TIMESTAMP) AS second FROM DUAL;
DB2
/* CURRENT A half width space is used between and TIME to specify the temporary table SYSIBM.SYSDUMMY1 */ SELECT CURRENT TIMESTAMP, EXTRACT(YEAR FROM CURRENT TIMESTAMP) AS year, EXTRACT(MONTH FROM CURRENT TIMESTAMP) AS month, EXTRACT(DAY FROM CURRENT TIMESTAMP) AS day, EXTRACT(HOUR FROM CURRENT TIMESTAMP) AS hour, EXTRACT(MINUTE FROM CURRENT TIMESTAMP) AS minute, EXTRACT(SECOND FROM CURRENT TIMESTAMP) AS second FROM SYSIBM.SYSDUMMY1;
5, Conversion function
Finally, I will introduce a special class of functions - conversion functions. Although some are special, they are easy to remember because their syntax is similar to the functions introduced before and the number is relatively small.
KEYWORD
- Conversion function
The word "conversion" has a very broad meaning. In SQL, it mainly has two meanings: one is data type conversion, which is called type conversion for short and cast in English [8] ; another layer means the conversion of values.
KEYWORD
-
Type conversion
-
cast
5.1 CAST - type conversion
Syntax 14 CAST function
CAST(Value before conversion AS Data type to convert)
CAST function is required for type conversion.
KEYWORD
- CAST function
Type conversion is required because data that does not match the data types in the table may be inserted, or errors may occur due to inconsistent data types during operation, or automatic type conversion will cause low processing speed. In these cases, data type conversion is required in advance (code listing 17 and code listing 18).
Listing 17 converts a string type to a numeric type
SQL Server PostgreSQL
SELECT CAST('0001' AS INTEGER) AS int_col;
MySQL
SELECT CAST('0001' AS SIGNED INTEGER) AS int_col;
Oracle
SELECT CAST('0001' AS INTEGER) AS int_col FROM DUAL;
DB2
SELECT CAST('0001' AS INTEGER) AS int_col FROM SYSIBM.SYSDUMMY1;
results of enforcement
int_col --------- 1
Listing 18 converts a string type to a date type
SQL Server PostgreSQL MySQL
SELECT CAST('2009-12-14' AS DATE) AS date_col;
Oracle
SELECT CAST('2009-12-14' AS DATE) AS date_col FROM DUAL;
DB2
SELECT CAST('2009-12-14' AS DATE) AS date_col FROM SYSIBM.SYSDUMMY1;
results of enforcement
date_col ------------ 2009-12-14
As can be seen from the above results, when converting a string type to an integer type, the preceding "000" Disappeared, and the conversion can be really felt. However, when converting a string to a date type, it is difficult to understand what has changed in the data from the results. From this point, we can also see that type conversion is not a function developed for the convenience of users, but a function developed for the convenience of DBMS internal processing.
5.2 COALESCE -- convert NULL to other values
Syntax 15 COALESCE function
COALESCE(Data 1, data 2, data 3)
COALESCE is a SQL specific function. This function returns variable parameters [9] The first non NULL value from the left in the. The number of parameters is variable, so it can be increased infinitely as needed.
KEYWORD
- COALESCE function
In fact, the use of conversion functions is very frequent. The conversion function is used when converting NULL to other values in SQL statements (code listing 19, code listing 20). As we learned before, when an operation or function contains NULL, the result will all become NULL. The function that can avoid this result is COALESCE.
Listing 19 converts NULL to another value
SQL Server PostgreSQL MySQL
SELECT COALESCE(NULL, 1) AS col_1, COALESCE(NULL, 'test', NULL) AS col_2, COALESCE(NULL, NULL, '2009-11-01') AS col_3;
Oracle
SELECT COALESCE(NULL, 1) AS col_1, COALESCE(NULL, 'test', NULL) AS col_2, COALESCE(NULL, NULL, '2009-11-01') AS col_3 FROM DUAL;
DB2
SELECT COALESCE(NULL, 1) AS col_1, COALESCE(NULL, 'test', NULL) AS col_2, COALESCE(NULL, NULL, '2009-11-01') AS col_3 FROM SYSIBM.SYSDUMMY1;
results of enforcement
col_1 | col_2 | col_3 -------+-------+----------- 1 | test | 2009-11-01
Listing 20 uses the columns in the SampleStr table as an example
SELECT COALESCE(str2, 'NULL') FROM SampleStr;
results of enforcement
coalesce ---------- rt def Taro 'NULL' xyz 'NULL' 'NULL' 'NULL' abc abc i
In this way, even if the column contains NULL, it can be converted to other values through the COALESCE function and then applied to the function or operation, so that the result is no longer NULL.
In addition, most DBMS provide special simplified functions of COALESCE (such as NVL in Oracle). However, since these functions depend on their own DBMS, it is recommended that you use the general COALESCE function.
see also
(end)
The reference documentation is part of the DBMS manual. You can also get relevant information from books introducing various functions and Web sites. ↩︎
Except for the COALESCE function in the conversion function. ↩︎
Because this is the same as the method of connecting strings in Java, it is estimated that some readers are familiar with it. ↩︎
MySQL also has its own function char to calculate the string length_ LENGTH. ↩︎
It should be noted that this function also has the same problem of multi byte characters as the LENGTH function. For details, please refer to the column "using the LENGTH function for one character may get more than 2 bytes of results". ↩︎
If you want to know the details of the date function, you can only consult the manuals of each DBMS at present. ↩︎
As we mentioned earlier, current cannot be used in SQL Server_ Date and CURRENT_TIME function. It may be because in SQL Server, CURRENT_TIMESTAMP has covered the functions of both. ↩︎
Type conversion is also used in general programming languages, so it is not a unique function of SQL. ↩︎
The number of parameters is not fixed. You can set the number of parameters freely. ↩︎