Other Functions and Interesting Notes
The following sections illustrate some of the other topics in this book and how those tasks are performed in the various database servers.
Substring Extraction
To extract a portion of a string in SQL, use this:
Server | Syntax |
|---|
MySQL | SUBSTRING(field, start, cchars) | PostgreSQL | SUBSTRING(field FROM start FOR cchars) | Oracle | SUBSTRING(field, start, cchars) | Microsoft SQL Server | SUBSTRING(field, start, cchars) |
String Concatenation
To concatenate two strings together and return them, use this:
Server | Syntax |
|---|
MySQL | CONCAT | PostgreSQL | || | Oracle | || | Microsoft SQL Server | + |
NOW
To get the current time in SQL, use this:
Server | Syntax |
|---|
MySQL | NOW | PostgreSQL | NOW | Oracle |
"SELECT CURRENT_TIME FROM dual;
SELECT SYSDATE FROM dual;"
| Microsoft SQL Server |
"SELECT GETDATE()
FROM nix"
|
Date Functions
The various servers have a large number of functions to manipulate and extract information from strings. Some places to start looking are as follows:
Server | Syntax |
|---|
MySQL | YEAR, MONTH, DAY, DAYOFWEEK, etc. | PostgreSQL | date_part | Oracle | EXTRACT datePart FROM DateTime | Microsoft SQL Server | DAY MONTH YEAR DATEADD DATEDIFF DAY GETDATE DATEPART |
Formatting of Date/Time Output
To format date and time values for output using SQL, use this:
Server | Syntax |
|---|
MySQL | DATE_FORMAT | PostgreSQL | to_char | Oracle | TO_CHAR | Microsoft SQL Server | CONVERT |
The LIMIT Clause
When we do not want to fetch all the rows in a table, but merely some subset thereof, we use this:
Server | Syntax |
|---|
MySQL | LIMIT start, num_to_fetch | PostgreSQL | LIMIT num_to_fetch OFFSET start | Oracle | SELECT XXX FROM YYY WHERE ROWNUM >= start AND ROWNUM < start + num_to_show | Microsoft SQL Server | Not supported (closest functionality is:
SELECT TOP 10 FROM Table;)
|
|
