On 31 Aug 2005 10:05:32 -0700, theintrepidfox@hotmail.com wrote:

> Dear Group
>
> The scaenario is as follows:
> FirstName and LastName are separate columns in the contact table and I
> want to be able to search e.g. for the FirstName and part of the
> LastName at the same time e.g. 'John A' should return 'John Adams'.
> Would be grateful if you can give me some hint as I don't seem to get
> it work.
>
> SELECT FirstName, Lastname FROM i2b_contact WHERE (SELECT Firstname +
> Lastname AS CName) LIKE 'John A%'
>
> Thanks very much for your help and efforts!
>
> Martin

Ross Presser wrote:
> On 31 Aug 2005 10:05:32 -0700, theintrepidfox@hotmail.com wrote:
>
> > Dear Group
> >
> > The scaenario is as follows:
> > FirstName and LastName are separate columns in the contact table and I
> > want to be able to search e.g. for the FirstName and part of the
> > LastName at the same time e.g. 'John A' should return 'John Adams'.
> > Would be grateful if you can give me some hint as I don't seem to get
> > it work.
> >
> > SELECT FirstName, Lastname FROM i2b_contact WHERE (SELECT Firstname +
> > Lastname AS CName) LIKE 'John A%'
> >
> > Thanks very much for your help and efforts!
> >
> > Martin
>
> SELECT FirstName, Lastname FROM i2b_contact
> WHERE Firstname + ' ' + Lastname LIKE 'John A%'
>
> You may do better, index-wise, to split it up:
>
> SELECT FirstName, Lastname FROM i2b_contact
> WHERE Firstname like 'John%' AND Lastname like 'A%'