There is no command. There are string functions. Allow me to demonstrate the approach to take, to understand and solve this particular example.
To start with, a database is all about data. Yes, a tautology, but that is where your attention should be focused first. Do not begin writing SQL until you have a good understanding of the data.
In this case we are dealing with middle initials in people's names. A single column containing people's names is not ideal, but a very common situation. We've all had to work with data like that. It comes in from some outside source, and we have to deal with it. So let's begin by looking at some sample data:
fullname Todd Kamalfechuk Johnny B. Goode Harry S Truman Mary "NMN" Smith G. Walker Bush Eddie Van Halen Cher
Of course, these examples disproportionately show their actual frequency of occurrence in real life. You will by far see mostly the first two formats. The others are there as outliers, and we never ignore outliers when testing, do we?
It is finally time to address the actual SQL to find those names which have no middle initial. This is where the magic happens. (Sorry, that's the best way to describe it.) You suddenly see that you want those names which have only one space (Todd), or none (Cher).
select fullname from pensioners where position(' ' in substring(fullname from position(' ' in fullname)+1)) = 0
Translating the requirement—one space or none—into functioning SQL—nested positions—isn't really magic, it simply requires experience, intuition, or diligent reading of the manual coupled with rigorous testing. The real magic is realizing you want to count spaces.
Another way to do it, if your database system's SQL has a REPLACE function, is to strip the spaces out and compare before and after character lengths:
where char_length(fullname) - char_length(replace(fullname,' ','')) <= 1
Eddie Van Halen has no middle initial, and both of these approaches will incorrectly omit names like his. It is prudent sometimes to ignore outliers. As anyone who has worked with names can attest, you should at some point use an "eyeball utility" (have a human actually read all the names). Even more caution is required in related problems like actually splitting a fullname into first and last names, which is a lot messier than just finding those with no middle initial.
Dig Deeper on Oracle development languages
Related Q&A from Rudy Limeback
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.