Applied Database Systems - Tutorial 4

This tutorial is a good chance for everyone to catch up with the previous tutorials. If you haven't completed the exercises of the previous tutorial, you may do so now since todays tutorial is rather short.

Note: There is a section in Tutorial 1 on how to redirect the output of a query into a file.

This tutorial consists of two parts:

 String Manipulation in PostgreSQL

This section describes functions and operators for examining and manipulating string values. Strings in this context include values of all the types character, character varying, and text. All of the functions listed below work on all of these types. A complete list of PostgreSQL string functions can be found in the PostgreSQL Documentation.

Here, we describe seven different string functions:

 String length

The function length returns the length of a string. The following query lists the names of cities and their length, ordered in ascending order of the length:

SELECT name, LENGTH(name) FROM cities ORDER BY LENGTH(name);
 Remove Preceding and Trailing Whitespace Characters

The function trim([leading | trailing | both] [characters] from string) removes the longest string containing only the characters (a space by default) from the start/end/both ends of the string. The following query returns Tom.

SELECT TRIM(both 'x' from 'xTomxx');

The following query return Tom as well (nore that ' ' and both are the respective default value for trim:

SELECT TRIM(' Tom  ');

There exist separate function ltrim and rtim that will remove characters only from the start or the end of the string.

 Upper and Lower Case

The function lower converts all the characters in a string into lower cases. Likewise, the function upper converts all characters in a string into upper cases.

The following query returns a list of distinct city names in lower case from relation weather:

SELECT DISTINCT(LOWER(city)) AS lcity FROM weather;

Question: Which query will produce the following output using relation weather, i.e., a distinct list of city names in upper and lower case?

     lcity     |     ucity
---------------+---------------
 hayward       | HAYWARD
 chicago       | CHICAGO
 london        | LONDON
 san francisco | SAN FRANCISCO
(4 rows)
 Substring Location

The function position(substring in string) returns the position of substring in string. For example, the following query will return the name of a city and the position of substring an within the name.

SELECT name, POSITION('an' IN name) FROM cities;

Note: The function position only returns the first occurrence of a substring (e.g., for San Francisco).

The function strpos(string, substring) also returns the location of the specified substring, but note the reversed argument order.

 Substring Extraction

The function substring(string [from integer] [for integer]) extracts a substring from a given string. The optional argument from specifies the starting position (the first character has position 1). If omitted, extraction will always start from position 1. Parameter for specifies the length of the extracted substring. If omitted, the whole substring starting at from and goint to the end of string will be extracted.

The following query returns the first character of city names in cities:

SELECT SUBSTRING(name FOR 1) FROM cities;

The following query will return cisco:

SELECT SUBSTRING(name FROM 9) FROM cities WHERE name LIKE 'San%';

The following query will return burg:

SELECT SUBSTRING('Edinburgh' FROM 5 FOR 4);

Question: Find out what happens when from is greater that the length of string? What happens when from + for is greater than the length of string.

Note: Function substr(string, from [, count]) does the same as substring(string from from for count)).

 String Concatenation

Strings can be concatenated using the following function: string || string. The following query precedes each city name with a fixed string:

SELECT 'University of ' || name from cities;

The following query lists the names of all cities with the first character in lower cases and all other characters in upper cases.

SELECT LOWER(SUBSTRING(name FROM 1 FOR 1)) || UPPER(SUBSTRING(name FROM 2))
FROM cities;
 String Replacement

The function replace(string text, from text, to text) replaces all occurrences in string of substring from with substring to.

The following (not very meaningful) query replaces every occurrence of an with on for names in cities:

SELECT REPLACE(name, 'an', 'on') FROM cities;
 Exercise
 Question 1

Which Ordnance Survey Grid square contains the most Munros?

Note: The format of the gridref value in relation mountains is as follows: LLxxxxyyyy where

  • LL denotes the grid square of size 100 km x 100 km,
  • xxxx denotes the distance to the east (i.e., the x co-ordinate) from the south west corner of each grid square (easting), and
  • yyyy denotes the distance to the north (i.e., the y co-ordinate) from the south west corner of each grid square (northing).

The precision of the easting and northing is in steps of 10m. For example, the position of Ben Nevis is 13.440m to the east and 44.090m to the north of the south west corner of grid square NN. More information about the Ordnance Survey Grid can be found on Wikipedia.

The output should look like this:

 grid_square | count
-------------+-------
 NN          |   137
(1 row)
 Question 2

Which Munro is nearest to the center of its respective Ordnance Survey Grid square?

Note: The following functions may be of interest:

  • sqrt(a) returns the square root of a.
  • pow(a, b) returns a raised to the power of b.
  • to_number(string, '9999') converts a string into a four digit integer.

The output should look like this:

             mname              | distance_from_centre
--------------------------------+----------------------
 Meall Buidhe [Glen Lyon North] |  21.0237960416286383
(1 row)
 Question 3

List the names of all Munros. Replace any occurrence of A' and a' in the Munro name with A and a, respectively.

Note: The character ' can be escaped in any string using ''.

The output should look like this:

               rmname
-------------------------------------
 A Bhuidheanach Beag
 ...
 Tom na Gruagaich
(283 rows)
 Question 4

Find the Munros with the longest name (excluding any additions in square or round brackets). Assume that a Munro contains additional information either in square or in round brackets but not in both.

The output should look like this:

             mname
-------------------------------
 Braigh Coire Chruinn-Bhalgain
 Mullach Coire Mhic Fhearchair
(2 rows)
End of Tutorial 4. Please send any corrections or suggestions to Heiko Müller.