Problem solve Get help with specific problems with your technologies, process and projects.

Extracting data from within a column

A SearchOracle.com member asks, "How do you write a command that will return data within a column? "

How do you write a command that will return data within a column? For example, I need to return the names of people that do not have a middle initial. What is the command to do this?

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:

Todd Kamalfechuk
Johnny B. Goode
Harry S Truman
Mary "NMN" Smith
G. Walker Bush
Eddie Van Halen

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.

This was last published in January 2008

Dig Deeper on Oracle development languages



Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.