Ask the Expert

How to write a query to search a field with just two characters?

A search by a field named (last name) must be capable of supporting a wild card search using at least the first two characters of the last name. What kind of query needs to written to search a field by just supplying the first two characters in a wild card search?

    Requires Free Membership to View

You will need to use the LIKE operator to implement the wildcard approach that you outlined above. I assume that you will be providing this capability to an on-line transaction whereby the user will enter the first several character of the last name as input on a screen. If so, your program should take those characters and then append percent signs to the end of the variable filling it entirely. So, for a variable of twenty bytes where the end user enters JA, your program would change that to JA%%%%%%%%%%%%%%%%%%. This allows DB2 to find any name that starts with the characters "JA".

You should also BIND your program with the REOPT(VARS) parameter to allow DB2 to use an index on this predicate. As long as at least one leading character is supplied DB2 can use an index. If, however, the leading character is unknown - that is, it is a percent sign (%) or underscore (_) - then DB2 would use an expensive table space scan. If you do not specify REOPT(VARS), then DB2 will not know what the variable contains and will likely resort to the scan for the query at BIND time. Of course, you could also use dynamic SQL to resolve this problem.

This was first published in January 2003

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: