Simple Text Query Help required

[bpeirson]

I currently have the following query which finds the user supplied text "[ENTER PROJECT ID:]" in the noted field.

SELECT * FROM ESTIM WHERE ((ESTIM.USER_TEXT2 = [ENTER PROJECT ID:])) ORDER BY ESTIM.USER_TEXT2 ;

Currently the records are returned only if the searched text is the leftmost text in the field.

What I need is to find the records where the supplied text is anywhere in the field, how can I do this?

Thanks a bunch.

 

[bborissov]

SELECT *
       FROM ESTIM
WHERE ESTIM.USER_TEXT2 LIKE '%[ENTER PROJECT ID:]%'
ORDER BY ESTIM.USER_TEXT2

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[bpeirson]

The following treats the statement [ENTER PROJECT ID:] as part of the string.

'%[ENTER PROJECT ID:]%'

The software I am using (Shoptech's E2) treats [ENTER PROJECT ID:] as a command to prompt the user for the search string. With the addition of the quotes that command is ignored.

Any other ideas?

 

[SQLSister]

It is almost never a good idea to search with a wildcard as the first character as it severely limits the use of indexes.

I'm not familiar with your front end but generally it is best to have it set the value of the data entry box to a variable and then execute a stored proc which uses that value as an input variable. Then you could concatenate the variable

SELECT *
       FROM ESTIM
WHERE ESTIM.USER_TEXT2 LIKE '%' + @PROJECT ID + '%'
ORDER BY ESTIM.USER_TEXT2

"NOTHING is more important in a database than integrity." ESquared

 

[bborissov]

Then put there the result.
Or use parameters as SQLSister suggested. by using parameters you can protect yourself from SQL Injections.
I am not familiar with Shoptech's E2 and can't give you an idea how to do this because I don't know HOW you deal with SQL Server.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP