Reputation:
I have a query that takes input from the end user and compares it to 2 columns individually, and the two columns concatenated.
SELECT f_name, l_name, (f_name + ' ' + l_name) AS full_name
FROM users_table
WHERE f_name = user-input
OR l_name = user-input
OR 'full_name' = user-input
Excuse the massive syntax fail, but I assure you it's correct in the program.
It is written in PHP, querying a SQL SERVER 2005 database, and is case insensitive.
Typing one name (first or last) will return correct results, but typing first, then space, then last, returns an empty set.
Any ideas?
Upvotes: 2
Views: 3584
Reputation: 10346
try:
SELECT f_name,
l_name,
(f_name + ' ' + l_name) AS full_name
FROM users_table
WHERE f_name = user-input
OR l_name = user-input
OR f_name + ' ' + l_name = user-input
Upvotes: 1
Reputation: 8513
Make sure that the user enters "full_name" (without the quotes) as the user-input and the database will return some rows.
Upvotes: 0
Reputation: 25263
This will work:
SELECT *
FROM users_table
WHERE CONCAT(first_name,' ',last_name) = user-input;
Upvotes: 1
Reputation: 45127
Under the "teach a man to fish" theory ...
You cannot use a column alias in a where or group by clause. You can use it in an order by clause. To use a column alias the way you want to, you would have to do something like ...
SELECT [Full Name] FROM
(SELECT f_name, l_name, f_name + ' ' + l_name AS [Full Name]
FROM users_table)
WHERE [Full_Name] = @paramVal
Upvotes: 5
Reputation: 14851
The problem is in this line:
OR 'full_name' = user-input
Provided that's actually what you have written down, you are comparing the literal string "full_name" to the provided input. Putting single quotes around something treats it as a literal. Try the following:
SELECT f_name, l_name, (f_name + ' ' + l_name) AS full_name
FROM users_table
WHERE f_name = user-input
OR l_name = user-input
OR full_name = user-input
Upvotes: 0
Reputation: 26940
'full_name' is a literal string not the column...
(f_name + ' ' + l_name) = user-input
Upvotes: 1
Reputation: 1467
For starters, remove the single quotes from around the full_name in the last where criteria. Now the user-input is compared to the string literal 'full_name'.
The concatenation part should be a-ok!
Upvotes: 0
Reputation: 135151
that is because 'full_name' is a literal and not a column name, it does not exists at the WHERE clause level
you need to add
OR f_name + ' ' + l_name = user-input
instead of OR 'full_name' = user-input
Upvotes: 10