SQLTeam.com | Weblogs | Forums

Can I use LIKE to search with variable data?


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.


I think this will get you started

SELECT * FROM ClientTable WHERE ClientName LIKE '%' + myVariable + '%'


Actually, here is the exact code I'm using:

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."

Any suggestions?


You need to setup full-text indexing to use CONTAINS as JamesK and the error message mentioned.


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.


Okay, forget "CONTAINS".
Try the percent sign query as it will get "Grand Hotel" and "Hotel Grand".

DECLARE @str VARCHAR(10) = 'Grand'
SELECT * FROM Client_Table WHERE Client_Name LIKE '%' + @str + '%';


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)
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.

-- Insert statements for procedure here

SELECT Employer_Code, Client_Name FROM Client_Table
WHERE (Client_Name LIKE '%' + @strEmpName + '%') OR (Employer_Code =

The error I'm getting is this:

I'm unable to add DECLARE as well.


You have a missing comma after the first parameter

ALTER PROCEDURE [dbo].[Client_Table_SP]

-- Add the parameters for the stored procedure here
@strEmpName VARCHAR(20) , ---<--- Need this comma
@strEmpCode VARCHAR(10)


That worked. I was able to save. Haven't had a chance to test to see if it works. Will try it out tomorrow. Thanks :smile: