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
Requires Free Membership to View
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.
This was first published in November 2001
Join the conversationComment
Share
Comments
Results
Contribute to the conversation