graham wrote:
> I need to extract a substring from the field 'Account' that is always between
> parentheses, but is not always the first text between parentheses:
>
> SUBSTRING(Account, PATINDEX('%(%', Account) + 1, PATINDEX('%)%', Account) -
> PATINDEX('%(%', Account) - 1)
>
> where Account='Patient (A) Name (PAT1000)'
>
> I'm looking for PAT1000 not A

"Uri Dimant" wrote:

> Hi
> DECLARE @st VARCHAR(50)
> SET @st='Patient (A) Name (PAT1000)'
>
> SELECT REVERSE(LEFT(REVERSE(@st), PATINDEX('% %', REVERSE(@st))))
>
>
>
>
>
> "graham" <graham@discussions.microsoft.com> wrote in message
> news:FF3F8026-CC3B-48E5-A021-6B0E5A631311@microsoft.com...
> >I need to extract a substring from the field 'Account' that is always
> >between
> > parentheses, but is not always the first text between parentheses:
> >
> > SUBSTRING(Account, PATINDEX('%(%', Account) + 1, PATINDEX('%)%',
> > Account) -
> > PATINDEX('%(%', Account) - 1)
> >
> > where Account='Patient (A) Name (PAT1000)'
> >
> > I'm looking for PAT1000 not A
> >
> > any suggestions?
> >
> > Thanks kindly!
> >
> >
>
>