A Gentle Introduction to

SQL

GISQ: How to use functions

Most SQL implementations offer a large number of functions to manipulate strings and perform complex calculations. Unfortunately each implementation has its own suite with different names.

Concatenate strings.
Substring: Extracting part of a string.
lower case
Finding a substring in a string
Formatting numbers to two decimal places.
Replace a NULL with a specific value
Conditional values
Get the date and time right now.
Format dates.


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. oracle variations: None	SELECT name \|\| ' is in ' \|\| region FROM cia WHERE name LIKE 'D%'	postgres oracle access mysql sqlserver

Substring: Extracting part of a string. Here we take two characters starting from position 4. 123456789 'Afghanistan' -> 'ha' 'China' -> 'na' 'Sri Lanka' -> ' L' oracle variations: None	SELECT name, SUBSTR(name,4,2) FROM cia WHERE region = 'Asia'	postgres oracle access mysql sqlserver

lower case UPPER CASE is similar. oracle variations: None	SELECT LOWER(name) FROM cia WHERE UPPER(region)='SOUTH AMERICA'	postgres oracle access mysql sqlserver

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. oracle variations: None	SELECT name, INSTR(name, ' '), SUBSTR(name,1,INSTR(name,' ')) FROM cia WHERE name LIKE '% %'	postgres oracle access mysql sqlserver

Formatting numbers to two decimal places. This rounds up or down oracle variations: A preceding 9 in the format string may be dropped, 0 is not	SELECT title, score, TO_CHAR(score,'9.00') FROM movie WHERE id <= 10	postgres oracle access mysql sqlserver

Replace a NULL with a specific value oracle variations: None	SELECT code, name, NVL(leader, 'NO LEADER RECORDED!') FROM party	postgres oracle access mysql sqlserver

Conditional values oracle variations: The DECODE function allows a list of possible values	SELECT title, score, DECODE(SIGN(score-8.5), 1, 'Excellent', 0, 'OK', -1, 'OK') FROM movie WHERE id<=10	postgres oracle access mysql sqlserver

Get the date and time right now. oracle variations: None	SELECT SYSDATE FROM dual	postgres oracle access mysql sqlserver

Format dates. oracle variations: Other format codes: AM, D (day of week), DDD (day of year), DY (abrv. day name), HH, MON (month in words)	SELECT TO_CHAR(SYSDATE, 'DAY MM-DD-YYYY HH24:MI:SS') FROM dual	postgres oracle access mysql sqlserver