Combining two fields in MSSQL for search.

[Goof]

Hello,

I'm working on a project where I want to search multile fields in a table based on user input. Is there a way to combine these fields in my SELECT statement (with an AS clause) so I can only do a WHERE clause on one field?

For instance, if I have a firstName and lastName field and I want to search by full name, I want to do something like:

SELECT (firstName & ' ' & lastName) as fullName WHERE fullName LIKE '%lee%';

...and have it pull both "Lee Jones" and "Bruce Lee".

I know that I could search both fields using an AND joiner, but I'm talking about 10 fields or so, and I'd like to just be able to join them into one search field.

Any ideas?

Thanks,
Goof

[MarcusJT]

So close, Goof!

This works for Access:

SELECT ([firstName] & " " & [lastName]) as fullName FROM Contacts WHERE ([firstName] & " " & [lastName])  LIKE '*lee*';

(You may well need to change those *s to something else for SQL Server)


M@rco

[JimBolla]

SELECT (ISNULL([firstName], '') + " " + ISNULL([lastName], '')) as fullName FROM Contacts WHERE (ISNULL([firstName], '') + " " + ISNULL([lastName], '')) LIKE '%lee%';

..handles any case where the fields may allow NULLs. (because string + NULL = NULL)

[MattR]

Remember that will table scan.

[Goof]

m@rco and crowdozer: thanks!

mattr: table scan?

Thanks,
Goof

[Goof]

crowdozer, when I use an AS clause, can I not use the renamed field in the where clause? Meaning, can't I do:

SELECT (isNull([firstName], '') + isNull([lastName], '')) as FullName WHERE FullName LIKE '%somtext%';

Thanks,
Goof

[JimBolla]

unfortunately not. it would be a great feature if we could. i think the reason its not already in there is probably because it could lead to naming conflicts, such as the case:

SELECT field1 as field2, field2 as field3 from table where field2 = 'blah'

...in the where clause, which value should "field2" refer to? the alias field2 or the actual field2?

[Goof]

crowdozer, thanks for the information.

In my opinion, "field2" should refer to [field1] where [field2] should refer to [field2]...at least that makes sense to me. Otherwise, it could always give us a name conflict error.

*shrug*
Goof

[MarcusJT]

AFAIK it's because the SQL query is really in two parts - it does the query on ALL the data (the WHERE part), and then returns the data that you want (the SELECT part).

Thus you can't use a field you have created in the SELECT part in the WHERE part, since it is not actually created until after the data has been queried!


M@rco

[JimBolla]

good explination, m@rco. that makes a lot of sense. i can't believe i didn't think of it that way.

[MattR]

Originally posted by Goof
mattr: table scan?

That means your SQL engine will read each and every row in an effort to find a match and will be very slow. It is explained more in MS SQL documentation for LIKE clauses.