A basic proof-of-concept... Actual code would be less, but since I don't know your table/field names, this is the full code, so anyone can verify it works, tweak it, etc.
--Search Parameters
DECLARE @SearchString VARCHAR(MAX)
SET @SearchString='Oakland;City;Planning' --Using your example search
DECLARE @Delim CHAR(1)
SET @Delim=';' --Using your deliminator from the example
--I didn't know your table name, so I'm making it... along with a few extra rows...
DECLARE @Projects TABLE (ProjectID INT, ProjectName VARCHAR(200))
INSERT INTO @Projects (ProjectID, ProjectName) SELECT 1, 'Oakland City Planning'
INSERT INTO @Projects (ProjectID, ProjectName) SELECT 2, 'Oakland City Construction'
INSERT INTO @Projects (ProjectID, ProjectName) SELECT 3, 'Skunk Works'
INSERT INTO @Projects (ProjectID, ProjectName) SELECT 4, 'Oakland Town Hall'
INSERT INTO @Projects (ProjectID, ProjectName) SELECT 5, 'Oakland Mall'
INSERT INTO @Projects (ProjectID, ProjectName) SELECT 6, 'StackOverflow Answer Planning'
--*** MAIN PROGRAM CODE STARTS HERE ***
DECLARE @Keywords TABLE (Keyword VARCHAR(MAX))
DECLARE @index int
SET @index = -1
--Each keyword gets inserted into the table
--Single keywords are handled, but I did not add code to remove duplicates
--since that affects performance only, not the result.
WHILE (LEN(@SearchString) > 0)
BEGIN
SET @index = CHARINDEX(@Delim , @SearchString)
IF (@index = 0) AND (LEN(@SearchString) > 0)
BEGIN
INSERT INTO @Keywords VALUES (@SearchString)
BREAK
END
IF (@index > 1)
BEGIN
INSERT INTO @Keywords VALUES (LEFT(@SearchString, @index - 1))
SET @SearchString = RIGHT(@SearchString, (LEN(@SearchString) - @index))
END
ELSE
SET @SearchString = RIGHT(@SearchString, (LEN(@SearchString) - @index))
END
--This way, only a project with all of our keywords will be shown...
SELECT *
FROM @Projects
WHERE ProjectID NOT IN (SELECT ProjectID FROM @Projects Projects INNER JOIN @Keywords Keywords ON CHARINDEX(Keywords.Keyword,Projects.ProjectName)=0)
I decided to mix a few different answers together into one :-P
This assumes you'll pass in a delimited string list of search keywords (passed in via @SearchString) as a VARCHAR(MAX), which -- realistically -- you won't run into a limit on for keyword searches.
Each keyword is broken down from the list and added into a keyword table. You'd probably want to add code to remove out duplicate keywords, but it won't hurt in my example. Just slightly less effective, since we only need to evaluate once per keyword, ideally.
From there, any keyword that isn't a part of the project name removes that project from the list...
So searching for "Oakland" gives 4 results but "Oakland;City;Planning" gives only 1 result.
You can also change the delimiter, so instead of a semi-colon, it can use a space. Or whatever floats your boat...
Also, because of the joins and what not instead of Dynamic SQL, it doesn't run the risk of SQL Injection like you were worried about.