How to Transfer Information Relative to Population
Example for sql statements to parse and transfer information on population size from exsting data into the new database structure.
Old version (Fig.1): 1 field called Population
New version : 4 fields (Size_min ; Size_max ; Unit ; Category)
Figure 1. Access table ‘spec’ from the old version of the SDF
- Step 1 : ‘cleaning’ the data by removing obsolete blanks
Original data
Cleaned data (Fig.2) (functions LTrim, RTrim, Trim). In order to delete blanks on both left and right sides, the function Trim is needed.
Figure 2. Cleaned data by using the following SQL statement
Field ‘POPULATIONtrim’: Trim([spec].[POPULATION])
Step 2 : extracting numbers for population size and population unit from the text string
In this example, all data in the field ‘POPULATIONtrim’, which end with the following characters : ‘i’, ‘r’ and ‘p’ indicate the unit of the population size. In our example, the function ‘Right’ is used to select the last character of the field ‘POPULATIONtrim’.
To extract the population size from the text string, the information on the ‘unit’ has to be excluded.
The following query is used to create the field called ‘Size’:
Left ([POPULATIONtrim]; instr ([POPULATIONtrim]; (right ([POPULATIONtrim];1)))-1)
Here, the query retrieves all characters (due to function left) of the field ‘POPULATIONtrim’ from the left to the end (excluding information on the unit). The length to report is indicated by the function instr which returns the first occurrence of a string (in this case, the first character on the right, minus 1).
- Step 3 : extracting minimum and maximum population size
In this example minimum and maximum size are separated by a hyphen (-). The following query allows to create two fields ‘Size_min’ and ‘Size_max’.
‘Size_min’ : All information from the left until the hyphen should appear in this field. If there is no hyphen, the complete information from the field ‘Size’ is taken.
‘Size_max’ : All information after the hyphen to the right should appear in this field. If there is no hyphen, the complete information from the field ‘Size’ is taken.
- Summary of the complete query
SELECT
spec.SITECODE,
spec.TAXGROUP,
spec.TAX_CODE,
spec.SPECNUM,
spec.SPECNAME,
Trim (spec.POPULATION) AS POPULATIONtrim,
spec.MOTIVATION,
Right ([POPULATIONtrim],1) AS Unit, Left([POPULATIONtrim],InStr([POPULATIONtrim],(Right([POPULATIONtrim],1)))-1) AS [Size],
IIf ([Size] Like "*-*", Left([Size],InStr(1,[Size],"-")-1),[Size]) AS Size_min,
IIf ([Size] Like "*-*", Right([Size],InStr(1,[Size],"-")),[Size]) AS Size_max
FROM spec;
- 1 -