Functions

Concatenate strings.
Concatenation means "stick strings together".
In this example we concatenate three strings, name and region are string attributes of the table, ' is in ' is a string literal.

SELECT name || ' is in ' || region
 FROM cia
 WHERE name LIKE 'D%'

Substring: Extracting part of a string.
We from position 1 (the beginning) we take two characters. 4.

'Afghanistan' -> 'Af'
'China'       -> 'Ch'
'Sri Lanka'   -> 'Sr'

The SQL standard insists on a horrible syntax:

SUBSTRING(name FROM 1 FOR 2)

Specific to Oracle

See also SUBSTR SELECT name, SUBSTR(name,1,2) FROM cia WHERE region = 'Asia'

lower case
UPPER CASE is similar.
SELECT LOWER(name) FROM cia
 WHERE UPPER(region)='SOUTH AMERICA'

Finding a substring in a string
Here we extract the first word of a country name. INSTR gives this position of one string within another, we use this and substring to pick out the first few characters.
SELECT name,
       INSTR(name, ' '),
       SUBSTR(name,1,INSTR(name,' '))
  FROM cia
  WHERE name LIKE '% %'

Formatting numbers to two decimal places.
This rounds up or down
SELECT name,
   population,
   ROUND(population/1000000,2),
   ROUND(population,-6)
  FROM cia
  WHERE region='North America'

Replace a NULL with a specific value
The SQL standard is COALESCE
SELECT code, name,
    COALESCE(leader, 'NO LEADER RECORDED!')
  FROM party

Conditional values
The SQL Standard is the CASE statement
SELECT
   title,
   score,
   CASE WHEN score>8.5 THEN 'Excellent'
        ELSE 'OK'
   END
   FROM movie
   WHERE 10>id

Get the date and time right now.
SQL Standard specifies CURRENT_TIMESTAMP, CURRENT_DATE and CURRENT_TIME. These are widely ignored :(
SELECT SYSDATE, SYSTIMESTAMP
 FROM nix

Format dates.
Many engines support the SQL standard - see Mimer for details.

Specific to Oracle

The function TO_DATE and TO_CHAR and the Datetime Format Elements is useful here:

SELECT TO_CHAR(wk, 'DD/MM/YYYY'), song
FROM totp
WHERE singer='Tom Jones'