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





Core Web Application Development With PHP And MYSQL
Core Web Application Development with PHP and MySQL
ISBN: 0131867164
EAN: 2147483647
Year: 2005
Pages: 255

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net