The first thing to note is simple: don't do it like that! Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.
When you concatenate strings, you cause problems because SQL receives commands like:
SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'
The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:
SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;
Which SQL sees as three separate commands:
SELECT * FROM MyTable WHERE StreetAddress = 'x';
A perfectly valid SELECT
DROP TABLE MyTable;
A perfectly valid "delete the table" command
And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.
So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?
The second is: CONCAT works fine on my system, even in the form you show:
SELECT * FROM MYTABLE WHERE concat('PID','PName','RoomNo') LIKE '%a%'
So ... you probably aren't using SQL Server, or MySql , or ... and need to google for CONCAT and the name of your DB engine...
The second is that that code won't do what you want, even on SQL Server, or MySQL, or ... it will either return all rows or none. This bit:
concat('PID','PName','RoomNo') likemeans you will always compare the fixed string "PIDPNameRoomNo" with your search string instead of the actual column data. Probably you want something like
concat(PID, PName, RoomNo) like
, but you may need some CASTing in there depending on your DB design.
Submit an article or tip