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.

Question about parametres passing between reports

Hi, all experts here,

Thank you very much for your kind attention.

I am having a question about the parametres passing between reports. Do we need to create totally the same parametres for the two reports even we only want to jump to the desired report on a particular parametre? E.g. report A got 2 parametres P1 and P2, we only want to jump to report B based on parameter P1 (that is, once I click on the value of P1 in report A, it will birng us to report B with that particular parametre value only), in this case, do we need to create totally the same parametres P1 and P2 in report B as well? or we only need to create P1 in report B? Hope the question is clear for your help.

Thank you very much in advance for your kind help and advices. And I am looking forward to hearing from you soon.

WIth best regards,

Yours sincerely,

As long as P2 can have an empty or null value, or has a default value, you can pass that from the first report.

Or you can set P2 to be a valid value from P1. Sometimes this could be an ALL value.

cheers,

Andrew

|||

Hi,Andrew,

Thanks a lot for your kind advices.

With best regards,

Yours sincerely,

sql