Hello. I'm working on a project that a data table stores a list of specific client names and their ID numbers and the users need to query the data table to see if the client they are working with are on this list. If so, these clients get a discount, if not, they pay full price. The people I'm developing this for would like to use a single word search term to look for their clients. Currently, if the client name is only one word long, the client can be found. I would like to use something similar to the query below, but not 100% sure on the syntax:
SELECT * FROM ClientTable WHERE ClientName LIKE myVariable
"myVariable" would contain any one word search term the users typed into an input box.
SELECT * FROM Client_Table WHERE Client_Name LIKE @strEmpName
The problem I'm facing is if the client is Walmart, and they type "walmart", they will find that client, but if the client is Grand Avenue United Methodist Church, and they type "grand", it will not find the client. Can I use CONTAINS like this:
SELECT * FROM Client_Table WHERE CONTAINS(Client_Name, @strEmpName)
You can use CONTAINS if you have a full-text index on the column. Otherwise, you should use what @djj55 suggested. Your query would then be:
SELECT * FROM Client_Table WHERE Client_Name LIKE '%' + @strEmpName + '%'
Querying this way makes it impossible for SQL Server to use any indexes you may have on the Client_Name column. If you are interested only in rows that start with the search term, then you can change your query as shown below, which then will allow the use of indexes on Client_Name column, thus making the query faster.
SELECT * FROM Client_Table WHERE Client_Name LIKE @strEmpName + '%'
It appears my solution is not fool proof. Seeing as there are some clients with similar names, they want a query to find all matches to what the user entered, then display the results. I tried using CONTAINS, but I get this error message when I try saving:
"Cannot use a CONTAINS or FREETEXT predicate on table or indexed view Client_Table because it is not full-text indexed."
I'm sorry...by "Any suggestions?", I meant are there any other ideas I can use? I should have clarified...my apologies. I'm unable to set Full-text Specification to "Yes", so I need an alternative way, if any, to be able to create the list the people are asking for. If not, I guess I can talk to the database admin and see if they can set it to allow the change.
Now I'm getting a different issue. I'm trying to select by client name or their employer code, whichever the user enters. Here's the stored procedure as it sits:
ALTER PROCEDURE [dbo].[Client_Table_SP]
-- Add the parameters for the stored procedure here @strEmpName varchar(20) @strEmpCode varchar(10)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT Employer_Code, Client_Name FROM Client_Table
WHERE (Client_Name LIKE '%' + @strEmpName + '%') OR (Employer_Code = @strEmpCode)
END
