SitePoint Sponsor |
|
User Tag List
Results 1 to 11 of 11
-
Jun 19, 2002, 07:23 #1
- Join Date
- Feb 2001
- Location
- Pittsburgh, PA
- Posts
- 1,154
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Combining two fields in MSSQL for search.
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:
Code:SELECT (firstName & ' ' & lastName) as fullName WHERE fullName LIKE '%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
-
Jun 19, 2002, 07:58 #2
- Join Date
- Jan 2002
- Location
- London
- Posts
- 3,509
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
So close, Goof!
This works for Access:
Code:SELECT ([firstName] & " " & [lastName]) as fullName FROM Contacts WHERE ([firstName] & " " & [lastName]) LIKE '*lee*';
M@rcoMarcusJT
- former ASP web developer / former SPF "ASP Guru"
- *very* old blog with some useful ASP code
- Please think, Google, and search these forums before posting!
-
Jun 19, 2002, 08:43 #3
- Join Date
- Dec 2001
- Location
- erie, pa
- Posts
- 1,130
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Code: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)-- JIM BOLLA
Wanna play Halo 2? My XBOX Live gamertag: crowdozer
-
Jun 19, 2002, 09:07 #4
- Join Date
- Jan 2001
- Location
- buried in the database shell (Washington, DC)
- Posts
- 1,107
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Remember that will table scan.
Matt - Sybase DBA / PHP fanatic
Sybase/MySQL/Oracle | I don't like MySQL
Download Sybase | DBForums.com - for all your RDBMS talk
-
Jun 19, 2002, 09:13 #5
-
Jun 19, 2002, 09:22 #6
-
Jun 19, 2002, 09:46 #7
- Join Date
- Dec 2001
- Location
- erie, pa
- Posts
- 1,130
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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?
-
Jun 19, 2002, 10:31 #8
-
Jun 19, 2002, 10:42 #9
- Join Date
- Jan 2002
- Location
- London
- Posts
- 3,509
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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@rcoMarcusJT
- former ASP web developer / former SPF "ASP Guru"
- *very* old blog with some useful ASP code
- Please think, Google, and search these forums before posting!
-
Jun 19, 2002, 10:43 #10
- Join Date
- Dec 2001
- Location
- erie, pa
- Posts
- 1,130
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
good explination, m@rco. that makes a lot of sense. i can't believe i didn't think of it that way.
-
Jun 19, 2002, 11:34 #11
- Join Date
- Jan 2001
- Location
- buried in the database shell (Washington, DC)
- Posts
- 1,107
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Originally posted by Goof
mattr: table scan?Matt - Sybase DBA / PHP fanatic
Sybase/MySQL/Oracle | I don't like MySQL
Download Sybase | DBForums.com - for all your RDBMS talk
Bookmarks