Help with SQL equivalent to what I know in Foxpro

[wtotten]

I need to find all the records in a table where CUSTNO $ ( a parameter).

In Foxpro I would do this:
cCustList = prompt user to enter in a string
SELECT * FROM customers WHERE custno $ cCustList

I need the same functionality for SQL Server. Somehow I'll need to use a parameter and the equivalent of the Foxpro $ operator.

I need help on what the syntax would be.

Thanks,
Bill

 

[baltman]

http://www.sql-tutorial.net/SQL-tutorial.asp

Depending on what you mean, basic syntax is:

WHERE custno LIKE 'Customer%'

or

WHERE custno IN ('Customer1','Customer2')

or

WHERE custno NOT IN ('Customer1','Customer2')

Brian

 

[Mike Lewis]

Bill,

The usual way of translating the VFP $ operator is to use SQL's LIKE clause.

Brian showed you how to do that. However, I would add another percent sign, like so:

WHERE custno LIKE '[b]%[/b]Customer%'

You might find it useful to glance at the article at

http://www.ml-consult.co.uk/foxst-38.htm,

which compares the syntax of SELECT in VFP and SQL Server.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[bborissov]

or

WHERE CHARINDEX('Customer',custno) > 0

Borislav Borissov

 

[wtotten]

Thanks for your help guys. However, one issue still; the parameter. The string value I am looking for in a field is a parameter in the query, not a literal value. How do I use a parameter in a LIKE clause?

Thanks,
Bill

 

[mkrausnick]

Not quite sure what your environment is, but if you are using SQL passthru from VFP to accomplish this, the format would be something like:

sqlexec(nHandle,"SELECT * FROM customers WHERE custno LIKE '%"+cCustList+"%'","c_customers")

Normally, in SPT you'd pass a parameter by using the VFP variable name preceded by a percent sign, but since that's the SQL LIKE wildcard, that won't work, so you construct the SQL string the hard way.

If user enters '54321', you want SQL to see the following:

SELECT * FROM customers WHERE custno LIKE '%54321%'

Mike Krausnick
Dublin, California

 

[mkrausnick]

(slaps forhead) Of course, Mike's right. My bad.

Mike Krausnick
Dublin, California