N.Ha N.Ha - 1 year ago 104
SQL Question

String Extraction between two characters

I'm working in Microsoft Access 2010 and I have a table and column [tblData]![Associate Name] with data formatted like Smith, John (123456). Obviously, this shows last name, first name, and the employee ID number (which can vary between 5 to 6 digits). My issue is that I need to extract the employee ID number from this string and I'm struggling to use the correct combination of

with the

Answer Source

Use InStr() to find the position of the ( character.

Next use Mid() to extract the substring starting one character after the (

And then Val() will give you a number from the remaining digits; it ignores anything after the last digit.

If you want the result as a string instead of number, you can use CStr() to convert it.

Here is an example from the Immediate window:

Associate_Name = "Smith, John (123456)"
? InStr(1, Associate_Name, "(")
? Mid(Associate_Name, InStr(1, Associate_Name, "("))
? Mid(Associate_Name, InStr(1, Associate_Name, "(") + 1)
? Val(Mid(Associate_Name, InStr(1, Associate_Name, "(") + 1))
? CStr(Val(Mid(Associate_Name, InStr(1, Associate_Name, "(") + 1)))
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download