Extracting data from within a column

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT SQL How to check SQL query construction with the Mimer Validator Using the SQL GROUP BY clause for counting combinations How to use an SQL CASE expression How to sort an SQL UNION query with special ORDER BY sequence How to use string functions to make an SQL join An SQL solution for a customer order homework problem How to use SQL's POSITION function with substrings Using SQL date functions to get totals for last three days Using CASE in the SQL ORDER BY clause What's the difference between an SQL inner join and equijoin? Oracle development languages How to check SQL query construction with the Mimer Validator Understanding SQL string functions The top advice from Oracle experts in 2008 What's the difference between an SQL inner join and equijoin? Using LEFT OUTER JOIN query to get zero row counts in SQL How to return multiple values for THEN clause in an SQL CASE expression Can I concatenate row values in SQL? Should I try to avoid a LEFT OUTER JOIN in SQL? Tips for derived tables in SQL and using FULL OUTER JOINs How to write an SQL query for two foreign keys to the same table

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

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.