Open Script Solution

Today I faced an interesting situation while getting the certain characters from a field and wanted to display them by using SELECT DISTINCT statement in MySQL database. Suppose we had thousand records in a table and we wanted to get the certain characters from the first until the certain position counted 6 characters from the right of all particular characters, and display them by using the SELECT DISTINCT statement. So, what would you do? Well, here is the solution I made in MySQL database.

Let’s take a following example. We have had already about six thousand records in a table named mytable. Let’s say that in one of the fields named myfield has the multiple diverse value.
For example:

JAKARTA 10120
JAKARTA 10120
JAKARTA 10246
JAKARTA 10710
JAKARTA 11721
JAKARTA 11842
JAKARTA 12210
JAKARTA 12714
JAKARTA 13220
JAKARTA 13562
JAKARTA 13587
JAKARTA 14210
BOGOR 16125
BOGOR 16219
BOGOR 16242
BOGOR 16255
TANGERANG 15124
TANGERANG 15125
TANGERANG 15233
BEKASI 17121
BEKASI 17131
BEKASI 17142
BEKASI 17145
BEKASI 17233
...

then, I want to display only the unique city value become like this:

JAKARTA
BOGOR
TANGERANG
BEKASI

In the other words, we want to avoid the 5 digits of postcode of the city, and get the unique value of the city. So here is the SQL script how you can get that unique city value:

SELECT DISTINCT(SUBSTR(myfield, 1, LENGTH(myfield)-6)) FROM `mytable`;

This SQL tells the MySQL:
“Get the unique value from the myfield field started from the first character until the sixth position counted from upon the right of the field value”.

You can see that we have included three functions in that SQL:

1. DISTINCT, this keyword has the purpose to get the unique value from the set of records which has the possibility contains of the same value.
2. SUBSTR, this keyword has the purpose to get the certain characters from the certain field based on from the certain started position of character until the last position that we can specify.
3. LENGTH, this keyword has the purpose to get how many characters that contained in a field.

That SQL script worked like a charm and had been tested in MySQL version 5.0.51b which included in the WAMPSERVER version 2.0 installation.