Link to home
Create AccountLog in
Avatar of snyperj
snyperjFlag for United States of America

asked on

Help with a query to pull data from within a string

I have an imported table that has 1 data field ('name') with the following records
American Stairs (AMS)
CherryWood (CHW)
DoRay Furnishings (DRN)

In a query, I need to separate the 3 character designation out from between the parenthesis... how can I do that in a query please?

The parenthesis always start 1 space after the name, and the designation is always three characters long (i.e. AMS, CHW)

Avatar of Saurabh Singh Teotia
Saurabh Singh Teotia
Flag of India image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
I think this should do it but cannot test at the moment.

SELECT Mid(name, Instring(name, "(") + 1, Instring(name, ")") + 1)

Open in new window

You may be better to just get the name from the database and then extract the 3 character designation
this will handle more than 3 letters in the parentheses

select Trim (Replace(Replace(Mid([Name], InStr([Name], "(")), "(", ""), ")", ""))
from tablex
And a second look at mine and realised it will not work, should have been

SELECT Mid(name, Instring(name, "(") + 1, 3)

Open in new window

And the minimum code:

NME: Left(Right([FieldName],4),3)

Avatar of snyperj


thanks to all, but this was first and all I needed