Comparison of common database functions

A problem occurs when processing PostgreSQL database functions.
In the SELECT statement, you need to determine whether the attribute of a field is null or a specific value,
In Oracle, we will use nvl decode and other functions. Similarly, MySQL will have ifnull and if functions.
Sometimes it gets mixed up and forgets which database supports this function.

Summarize some common functions in different databases for future use.

I. Date operations

1. Current date and time of operation

Microsoft SQL Server
Select getdate ()
Go

MySQL return date does not include time
Select curdate ();

MySQL return date and time
Select now ();

Oracle
Select sysdate from dual;

PostgreSQL
Select current_date;
Select now (); return date time also includes the time zone

2. Obtain the subdomain of the operation time

Microsoft SQL Server
Select datepart (DW, getdate ())
Go

MySQL
Select dayname (curdate ());

Oracle
Select to_char (sysdate, 'day ')
From dual;

PostgreSQL
Select date_part ('dow', date 'right'); // Dow = day of week
Select date_part ('hour', timestamp 'right ')

In Microsoft SQL, the syntax for calling the function datepart is: datepart (datetype, date_expression). Function Parameters

Datetype is month, day, week, day of week, etc. The second parameter is a field containing the date type or a real

In MySQL, The dayname function directly specifies the current date of the week. in Oracle, to_char can be

Obtain the required subdomains, date, hour, and minute in the mid-term.

3. Time Interval. In some applications, you need to know how far the two time intervals are.

Microsoft SQL Server
Select datediff (DD, '2014/1/01', getdate ())
Go

MySQL
Select from_days (to_days (curdate ())-
To_days ('2017-11-25 '));

Oracle
Select to_date ('25-Nov-2000 ', 'dd-mon-yyyy ')-
To_date ('25-Aug-1969 ', 'dd-mon-yyyy ')
From dual;

PostgreSQL

Select age (current_date, '25-Aug-1969 ');

The function syntax varies greatly between different databases during different measurement intervals.

4. format the date and time

Microsoft SQL Server
Select convert (varchar (11), getdate (), 102)
Go

MySQL
Select date_format (\ "2001-11-25 \", \ "% m % E, % Y \");

Oracle
Select to_char (sysdate, 'dd-mon-yyyy hh: MI: SS Ps ')
From dual;

PostgreSQL
Select to_char (timestamp (current_date), 'dd-mon-yyyy hh: MI: SS Ps ');

Ii. String operations

1. The string contains characters

Microsoft SQL Server
Select charindex ('eat', 'great ')
Go

MySQL
Select position ('eid' in 'great ');

Oracle
Select instr ('great', 'eat') from dual;

PostgreSQL
Select position ('eid' in 'great ');

The above functions can be used to determine the position of a string in another string (and the position where another string contains this string ).

2. Remove spaces from strings

Microsoft SQL Server
Select ltrim ('SQL _in_a_nutshell '),
Select rtrim ('SQL _ in_a_nutshell '),
Select ltrim (rtrim ('SQL _in_a_nutshell ')
Go

MySQL
Select ltrim ('SQL _in_a_nutshell '),
Select rtrim ('SQL _ in_a_nutshell '),
Select trim ('SQL _in_a_nutshell '),
Select trim (both from 'SQL _in_a_nutshell ');

Oracle
Select ltrim ('SQL _in_a_nutshell '),
Select rtrim ('SQL _ in_a_nutshell '),
Trim ('SQL _in_a_nutshell ')
From dual;

PostgreSQL
Select trim (leading from 'SQL _in_a_nutshell '),
Trim (trailing from 'SQL _ in_a_nutshell '),
Trim (both from 'SQL _in_a_nutshell ');

3. Clear the opposite space and add a space.

Microsoft SQL Server
Not Supported

MySQL
Select lpad ('SQL _ in_a_nutshell', 20 ,''),
Rpad ('SQL _ in_a_nutshell', 20 ,'');

Oracle
Select lpad ('SQL _ in_a_nutshell', 20 ,''),
Rpad ('SQL _ in_a_nutshell', 20 ,'')
From dual;

PostgreSQL
Select lpad ('SQL _ in_a_nutshell', 20 ,''),
Rpad ('SQL _ in_a_nutshell', 20 ,'');
The functions of the databases that support this operation are the same and both include adding spaces from left to right.

4. String replacement

Microsoft SQL Server [returns 'wabbit _ hunting_season ']
Select stuff ('wabbit _ season ', 7, 1,' _ hunting _')
Go

MySQL [returns 'wabbit _ hunting_season ']
Select
Replace ('wabbit _ season ', 'it _', 'it _ hunting _');

Oracle [returns 'wabbit _ hunting_season ']
Select
Replace ('wabbit _ season ', 'it _', 'it _ hunting _')
From dual;

PostgreSQL
Select translate ('wabbit _ season ', 'it _', 'it _ hunting _');
Select Replace ('wabbit _ season ', 'it _', 'it _ hunting _');

5. String Truncation

Microsoft SQL Server
Select substring ('wabbit _ duck_season ', 7, 11)
Go

MySQL
Select
Substring ('wabbit _ duck_season ', 7, 11 );

Oracle
Select substr ('wabbit _ duck_season ', 7, 11)
From dual;

PostgreSQL
Select substr ('wabbit _ duck_season ', 7, 11 );

Iii. Condition judgment

1. Condition judgment
Microsoft SQL Server
Select case
When Foo = 'Hi' then 'there'
When Foo = 'good' then 'bye'
Else 'default'
End
From T2
Go

MySQL
Select if ('11' = '11'), '1', '2 ')
Select if (2> 1, '1', '2 ')

Oracle
Select decode
(Payments_info, 'cr ', 'credentials', 'db', 'debit', null)
From dual;

PostgreSQL
Select case
When Foo = 'Hi' then 'there'
When Foo = 'good' then 'bye'
Else 'default'
End
From T2;

The above functions are easy to understand.
Let's take a look at the mysql if () function. If the first parameter is true, the second parameter is returned. Otherwise, the third parameter is returned.

2. Judge empty Functions

Microsoft SQL Server
Select isnull (Foo, 'value is null ')
Go

MySQL
Select ifnull (122, 'aaa ')

Oracle
Select nvl (Foo, 'value is null ')
From dual;

PostgreSQL
Select coalesce (Foo, 'value is null ')

3. The following functions are different from the preceding functions.

Microsoft SQL Server [returns NULL when Foo equates to 'wabbits! ']
Select nullif (Foo, 'wabbits! ')
Go

MySQL
N/

Oracle
Select decode (Foo, 'wabbits! ', Null)
From dual;

PostgreSQL
Select nullif (Foo, 'wabbits! ');
Function Syntax: nullif (expression1, expression2)
If expression1 is equal to expression2, null is returned. If expression1 is null, null is also returned.