Various functions in SQL

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.

Programmer Help

Various functions in SQL

1, Types of functions

2, Arithmetic function

2.1 ABS - absolute value

2.2 MOD -- remainder

2.3 ROUND - round

3, String function

3.1 | - splicing

3.2 LENGTH -- string length

3.3 LOWER -- lowercase conversion

3.4 REPLACE - string replacement

3.5 SUBSTRING -- interception of string

3.6 UPPER - upper case conversion

4, Date function

4.1 CURRENT_DATE - current date

4.2 CURRENT_TIME - current time

4.3 CURRENT_TIMESTAMP - current date and time

4.4 EXTRACT - intercept date element

5, Conversion function

5.1 CAST - type conversion

5.2 COALESCE -- convert NULL to other values

see also

Hot Tags