×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

SQL Select LIKE syntax in FP98

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.

RE: SQL Select LIKE syntax in FP98

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 "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

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close