Ask the Expert

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

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
  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

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: