Extracting a part number from a text field

Our database contains a text field that users input information in that is not in a specific format. We need to...

extract data (specifically a part number) from this field for reporting purposes. What would be the best method to extract this information? The part numbers always begin with a 0, 1, 5 or 7 and are 7 characters in length.

If the text field is exactly 7 characters long, then it either contains a part number or it doesn't --

     select textfield
       from yourtable
      where substring(textfield from 1 for 1)
            in ('0','1','5','7')

If it's longer than 7 characters, you're in trouble. You want my honest answer? Write a program. In robust databases like Oracle and SQL/Server, the program can be a stored procedure. But do yourself a favour and don't try it in SQL.

To get an idea how tough this is, consider that the SQL has to extract a 7-character substring from somewhere within the text field, starting at the first position within the text field where a certain character is found. The problem is, you don't have too many tools -- expressions and functions -- at your disposal for this kind of processing.

Let's start by extracting part numbers that begin with a 5 --

     select substring( textfield
                  from position('5' in textfield)
                   for 7 )
       from yourtable
      where position('5' in textfield) > 0

The WHERE condition ensures that you actually do find a '5' somewhere in the text field. That's important, because the POSITION function returns a 0 if it doesn't find what it's looking for, and then the SUBSTRING function would end up taking the first six characters of the text field (since position 0 is outside of the string).

However, the above query will still produce incorrect results. Consider the following rows --

    row   textfield
     1    part number 5093705
     2    5234567 is the part number
     3    i wanted 5 widgets, fred, not 3
     4    6365937 is the part number

The query above will return

      5093705
      5234567
      5 widge
      5937 is

So perhaps you need to look for ' 5' instead, i.e. with a leading blank. But that wouldn't find the 2nd row above, so you'd have to test for that condition separately...

     select substring( textfield
                  from position(' 5' in textfield) + 1
                   for 7 )
       from yourtable
      where position(' 5' in textfield) > 0
  union all
     select substring( textfield
                  from 1
                   for 7 )
       from yourtable
      where substring( textfield 
                  from 1
                   for 1 ) = '5'

This would still pick up rows 3 and 4 (incorrectly). And that's only the beginning of our problems, because we've only done part numbers beginning with a 5.

What you really want is a stored procedure that can loop through the text field character by character. PL/SQL and Transact-SQL can do this -- contact your resident database programmer.

For More Information

• The Best SQL Web Links: tips, tutorials, scripts, and more.
• Have a SQL tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
• Ask your technical SQL questions--or help out your peers by answering them--in our live discussion forums.
• Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.