SQL Select LIKE syntax in FP98
SQL Select LIKE syntax in FP98
(OP)
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.
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.
RE: SQL Select LIKE syntax in FP98
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 "Name" 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.
RE: SQL Select LIKE syntax in FP98
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.