Extracting a substring from a variable position in a string

I would like to know how to search a variable length field (this is a sample of the field - /TRAK ADF/LQ QC/UNN1 3143). I would be searching for 'UNN1' within this field, and it can be anywhere within the field. Once it is located, I need the next five characters after it.

    Requires Free Membership to View

    Login

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


This was first published in October 2002

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top