question about query part of a string

I'm passing a variable to SQL and I want it to query a column (IP_user), but query any part of what is given. For example if I given Chris it would bring up Chris, Christian, Christine, etc. What is the syntax to do this? thanks

Try this

ALTER PROCEDURE [dbo].[SPU_XXXX]

@.SEARCH VARCHAR(30) = NULL

AS

SET @.SEARCH = '%'+ @.SEARCH + '%'

SELECT CLIENT.CLIENT, CLIENT.CLIENTID

FROM CLIENT

WHERE (CLIENT.CLIENT LIKE @.SEARCH)

|||

The best reference for this question is here:

http://www.sommarskog.se/arrays-in-sql.html

The Table of Numbers method is the easiest, fastest, etc. It will put the data into a table format that you can join to your table in a very straightforward fashion.

http://www.sommarskog.se/arrays-in-sql-2005.html#tblnum

|||

Code Snippet

Set @.search = 'Chris'

--To get all names start with Chris

--i.e., Chrisxxxxxxxx

select * from yourtable where ip_user like @.search + '%'

--To get all names ends with Chris

--i.e., xxxxxChris

select * from yourtable where ip_user like '%' + @.search

--To get all names containes Chris at anyplace

--i.e., xxxxxxChrisxxxxxxxx or xxxxxChris or Chrisxxxxxxxx

select * from yourtable where ip_user like '%' + @.search + '%'

|||

You really should look into Fulltext, as that is its strong feature. Another option using sql engine is to use charindex() function.

e.g.

select *

from tb

where charindex(@.var,your_column)>0

|||

Thank you that was very helpful and it worked great. I have another quick question, and although this isn't technically an access forum it involves access. I have someone who runs access and with an odbc connection, connects to the sql server database. Is there any sql that can be used in accesses queries to replicate this? I apologize ahead of time for posting this here, I couldn't find an access forum...

|||In Access, mark the Query as a 'Pass Though' query, and the query as above will be sent to execute on SQL Server without Access attempting to check syntax and compile.