Syntax? - Searching combined columns

[theintrepidfox]

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]

On 31 Aug 2005 10:05:32 -0700, th************@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%'

[theintrepidfox]

Thanks Ross! Your help is very appreciated!
Ross Presser wrote:

On 31 Aug 2005 10:05:32 -0700, th************@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%'