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;)