Standard SQL provides two string functions which you could use for this problem:
POSITION(substring IN string)
SUBSTRING(string FROM start FOR length)
The POSITION function allows you to search the string column to find the position of the substring. The result will be either a positive number or a zero, which indicates the searched substring does not exist in the string. Using the position number, you can create an expression for the start parameter of the SUBSTRING function.
Unfortunately, support for these functions is spotty. Each database system seems to have its own versions, with subtly different syntax, which you will have to look up in your documentation. Here's what the solution would look like in Microsoft Access, assuming your table is called TheTable and the column you're searching is called TheColumn:
select mid(TheColumn ,instr(TheColumn,'UNN1') + 4 ,5) from TheTable where instr(TheColumn,'UNN1') > 0
Mid is the substring function, and instr is the position function. Note that you need the WHERE clause test, in order to ensure that the column actually contains the substring you're searching for, because otherwise you'd get the 4th through 8th characters.
For More Information
- Dozens more answers to tough SQL questions from Rudy Limeback.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs 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, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in October 2002