[vc_row][vc_column][vc_column_text]I have seen a lot of questions on StackOverflow and SoloLearn regarding how to create a simple MySQL query that queries a list of names beginning with a vowel without displaying duplicates.  I read some of the answers and they were all over the place. I decided that if a programmer in training wants to learn this, there needs to be more explanation to help a new programmer understand what is being done when the call is made to the database.  Furthermore, I have added some useful resources to make the process of a learning much easier.

 

Query a Row and Display Only the items whose name begins with a Vowel with No Duplicates

 

The Process: How you Reframe the Question for you:

Since programming is a lot like math and statistics, it is a good idea to reframe a question to make it into your own terms. When I’m trying to find a solution, I think of the problem literally. For example,

“I have to find the row called “ROW_NAME” that is in the “TABLE_NAME” table and return the names that start with a vowel and make sure that there are no duplicates.”

 

If I examine the problem, I can begin to look for a solution that will meet these requirements.  If I was a newbie, I would start by searching for “how to use MySQL to query a list of names from a row that begin with a vowel only without duplicating.”

Google will likely give these results:

Most of the sites will be from forums that allow users to ask and answer questions and help one another.  As a newbie, these websites can make you feel really inadequate because many seasoned programmers are just not patient if you haven’t tried to figure out your problem alone.  The best options are to read comments on others questions.  Look for references of where the user pulled the information and read.  Learning to program is extremely difficult and in order to learn, one must be willing to READ everything they can about syntax’s, basics, advanced functionality and more. The only way to truly learn programming is to make mistakes.

Going back to our question, I have laid out my personal and literal understanding of the question and planned a map to solve it. I know that I have to select the distinct (different) rows within the ROW_NAME row that is located in (or coming FROM) the TABLE_NAME table and I want the returned values to be only the ROW_NAME names that’s first letter is a vowel. So the SELECT DISTINCT will pull the information I ask for without duplicates, the FROM will call the TABLE_NAME table where I am asking for the ROW_NAME row data, the WHERE will specify that we are asking for the city and the REGEXP (regular expression) is asking for the condition matching ‘^’ (where ^ is the beginning of the string) and [aeiou] (are the letters we are searching for at the beginning of the string). By using the single quotes around our regular expression value (in this case ‘^[aeiou]’), we are calling the string literals (or the values that match literally the data we want starting with the letters a, e, i, o, or u).

This is the solution that works with everything you have read above (if you are using MySQL):

Note: Please change out ROW_NAME for the name of the row you are looking for and TABLE_NAME to the table you are looking for in all of the following examples.

[sql]SELECT DISTINCT ROW_NAME FROM TABLE_NAME
WHERE ROW_NAME REGEXP ‘^[aeiou]'[/sql]

A similar Query would be to find the vowels at the end without duplicates:

[sql]SELECT DISTINCT ROW_NAME FROM TABLE_NAME
WHERE ROW_NAME REGEXP ‘[aeiou]$'[/sql]

One more similar query is the vowels at the beginning and end of the results without duplicates:

[sql]SELECT DISTINCT ROW_NAME FROM TABLE_NAME
WHERE ROW_NAME REGEXP ‘^[aeiou].*[aeiou]$'[/sql]

Another query might be either/or.  You might be asked to grab either doesn’t start with a vowel or end with a vowel.

[sql]SELECT DISTINCT ROW_NAME FROM TABLE_NAME
WHERE ROW_NAME REGEXP ‘^[^AEIOU]|[^AEIOU]$’
[/sql]

There are many more examples of using regular expressions to get a query done and get the expected results.  Take your time to search and use online coding playgrounds to test your syntax, to inform you of errors and to make sure you are not missing critical elements. Furthermore, companies like hackerrank.com offer great training programs that force programmers to learn how to be a researcher and find a solution to an issue.

Where to Find Help:

Here are some resources I have located that I believe are super helpful in understanding MySQL SELECT DISTINCT and Regular Expressions. Click here to see this table and more information. or Check out this reference

Here is a table of all the Regular Expressions Pattern Operators, what they are and examples of how to use them:

Pattern
Behaviour
Example of Use
^
Beginning of string
REGEXP ‘^[abc]’ – will find anything that begins with abc
$
End of string
REGEXP ‘[abc]$’ – this will find anything that ends with a, b, or c
.
Any single character
REGEXP ‘^.{5}$’; – will find the item whose name has 5 characters
[ .. ]
matches any character specified inside the square brackets
REGEXP ‘^[abc]’ – this will look for a, b, or c as indicated within the brackets
[^ ..]
matches any character not specified inside the square brackets
REGEXP ‘^[^abc]’ – will pull data that doesn’t start with a, b, or c
pattern 1 | pattern 2
Alternation; matches any of the patterns
REGEXP ‘^[abc]|def$’; – will pull anything that starts with a, b, or c and ends with de
*
matches the preceding character zero or more times
REGEXP ‘^[abc].*[er]$’ – Will find anything that starts with a,b or c and ends with an e or an r
+
matches preceding character one or more times
REGEXP ‘mons+’ – will pull anything that ends with mons and whatever proceeds it. Example, “lemons”
{n}
matches n number of instances of the preceding character
REGEXP ‘a{2}’ – in this case {n}={2} and we are asking for 2 occurrences of a.
{m,n}
matches from m to n number of instances of the preceding character
REGEXP ‘a{2,4}’ – in this case {n,m}={2,4} and we are asking for 2 but no more than 4 occurrences of a.

SELECT DISTINCT using MySQL (great for beginners):  https://www.w3schools.com/sql/sql_distinct.asp

Regular expression can be confusing but here is good reference (perfect for newbies and intermediate users): https://www.tutorialspoint.com/mysql/mysql-regexps.htm

MySQL Reference Manual for Regular Expressions (This is more for intermediate to advanced users trying to go back to the basics):
https://dev.mysql.com/doc/refman/5.7/en/regexp.html

MySQL: Search Based on Regular Expressions (great article with lots of examples for beginners):   http://www.mysqltutorial.org/mysql-regular-expression-regexp.aspx
https://dev.mysql.com/doc/refman/5.7/en/regexp.html#operator_regexp

Complete Tutorial of Regular Expressions from Princeton (More advanced applications):  https://www.princeton.edu/~mlovett/reference/Regular-Expressions.pdf

SQL Pattern Matching (Great resource for pattern matching applications): http://doc.nuodb.com/Latest/Content/SQL-Pattern-Matching.htm[/vc_column_text][/vc_column][/vc_row]

%d bloggers like this: