Comparison of common database functions

Welcome to the Windows community forum and interact with 3 million technical staff when you are processing postgreSQL database functions today. I have encountered a problem. In the select statement, I need to judge whether the attribute of a field is null or a specific value, in oracle, we will use nvldecode and other functions. Same m

Welcome to the Windows community forum and interact with 3 million technical staff> when you are processing postgreSQL database functions today. I have encountered a problem. In the select statement, I need to judge whether the attribute of a field is null or a specific value, in oracle, we use functions such as nvl decode. Same m

Welcome to the Windows community forum and interact with 3 million technicians>

Today, we are processing postgreSQL database functions. I have encountered a problem. In the select statement, I need to judge whether the attribute of a field is null or a specific value, in oracle, we use functions such as nvl decode. Mysql also has ifnull and if functions. Sometimes it gets mixed up and forgets which database supports this function.

So I will summarize some common functions in different databases, so that they can be used in the future.

1. Date operation 1: operate on the current date and time Microsoft SQL Server Select GETDATE ()

GO MySQL return date does not include the time Select CURDATE (); MySQL returns the date and time Select NOW (); oracle Select sysdate from dual; PostgreSQL Select CURRENT_DATE; Select NOW (); the return date and time also includes the time zone 2, the operation time acquisition subdomain 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 'now '); // dow = day of week Select DATE_PART ('hour', timestamp 'right ')

The syntax for calling the DATEPART function in Microsoft SQL is: DATEPART (datetype, date_expression ). The function parameter datetype is month, day, week, day of week, etc. The second parameter is a field containing the date type or a real date value, in mysql, The DAYNAME function directly specifies the day of the week for the current date. In oracle, TO_CHAR can obtain the required subdomain, date, hour, and minute from the date.

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 '); measure different time intervals, and function syntax varies significantly between different databases.

4. Format 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 PM ')

FROM dual; PostgreSQL Select TO_CHAR (timestamp (CURRENT_DATE), 'dd-Mon-yyyy hh: mi: ss Ps'); 2. String operation 1, the string contains the character Microsoft SQL Server Select CHARINDEX ('eat', 'great ')

GO MySQL Select POSITION ('eat' 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 of another string containing this string ).

2. Remove spaces from the string. 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, to clear the opposite space, add the 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. Replace the string with 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); 3. Condition judgment 1, conditional 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 ', 'cred', '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 not explained much. It is easy to understand. Let's talk about 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/A 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.