SQL Select LIKE syntax in FP98

[Gcox]

I am using the Database Region Wizard in FrontPage 98 to set up a simple SQL statement. I was hoping someone could give me a couple of examples using the "LIKE" syntax with a variable called varName. One example I found suggested this:

SELECT * FROM tblAddress WHERE [Name] Like '%%%varName%%%'

But this returns all the records regardless of what is in varName, and I've tried several different variations and nothing works.

 

[Guest_imported]

If the FP database handles SQL the way other databases do you should perform a select in this fashion:

SELECT * FROM tblAddress WHERE [Name] LIKE 'varName'

The % is a wildcard character to allow you to get results where the varName matches a portion of the table field.

For instance, if you have a field in your table called "Name" and one of the records with the field "Name" has the contents of "Gregory" and another record has "Greg" you can perform a select statement to pull both of these records using the % wildcard character in this way:

... WHERE [Name] LIKE 'Greg%'

This condition will pull all records that meet the criteria of having the first four letters of 'Greg' in the field [Name].

You will have to be careful with some databases as they are case sensitive. So 'GREG' and 'Greg' are not the same. You can alleviate this problem with a function called UPPER (may or may not be supported on the FP database).

Select * from <tablename> where upper(Name) like(upper('varName'))

This statement will cast the field &quot;Name&quot; and the varName to upper case in order compare apples to apples.

As for your select statement, the '%%%varName%%%' will retrieve all records that contain varName within the field. This is because you specified the % on both sides of the input host variable.

 

[Guest_imported]

I had one more thought about why all the records are being retrieved. If your input host variable is blank, i.e. the data is not being retrieved from the application, you will probably get a match on almost every row in your table.

One way to test this is to put a name into the select statement rather than the input host variable and peform the select statement. It should return rows with that particular value.