Find SQL search rules

When you search for a SQL statement from the Find SQL tab in DPA, the following rules apply:

• Rules for all searches
• Rules for searches in simple mode
• Rules for searches in advanced mode

Rules for all searches

The following rules apply to searches in both simple and advanced modes:

• Searches are not case sensitive. For example, SELECT and select return the same results.

• You can enter two types of search entries:

  • A term is a single word, such as select. You can enter multiple terms separated by spaces. For example:

    select where

    The terms do not have to occur together in the SQL statement.

  • A phrase contains multiple words surrounded by double quotes. For example:

    "select userid"

    Use phrases to search for terms that must occur together in a specific order.

• If you include multiple entries, there is an implicit OR between each entry. For example:

  • Entering select userid returns SQL statements that include either select or userid.
  • Entering "select userid" count returns SQL statements that contain either select userid or count.

  SQL statements that include all entries are ranked higher in the search results.

• Partial terms without wildcards are not matched. For example, searching for sel does not return SQL statements that contain select.

  Wildcards are supported only in advanced mode.

Rules for searches in simple mode

• Special characters do not need to be escaped in simple mode.

• You can enter multiple terms, but you cannot enter more than one phrase in simple mode. For example, you can enter:

  "select id" employees status active

  If you enter multiple phrases in simple mode, DPA treats everything between the first " and the last " as one phrase. For example, the following phrases are combined:

  "select userid" "from employees"

  SQL statements that contain select id from employees are returned, but SQL statements that contain (for example) select id, lastname from employees are not returned.

• Simple mode does not support Boolean operators, wildcards, grouping, or other options described in the following section.

Rules for searches in advanced mode

The following options are available in advanced mode:

• Multiple phrases
• Boolean operators
• Wildcards
• Fuzzy searches
• Proximity searches
• Grouping
• Escaping special characters

Multiple phrases

In advanced mode, you can enter multiple phrases separated by spaces or Boolean operators. For example:

"select count" "where type='user'"

Boolean operators

In advanced mode, you can use the following Boolean operators. Operators that are words (OR, AND, NOT) must be entered in all caps.

Operator	Description
OR	Returns SQL statements that include either term or phrase anywhere in the statement. The following example returns SQL statements that include either select or insert: select OR insert SQL statements that include both terms are at the top of the search results. OR is the default operator. If you enter multiple terms without an operator, OR is used.
AND	Returns SQL statements that include both terms or phrases anywhere in the statement. The following example returns SQL statements that include both the phrase select username and the term mgmt_user: "select username" AND mgmt_user
NOT	Excludes SQL statements that include the term or phrase that immediately follows the operator NOT. The following example returns SQL statements that include select but do not include username:  select NOT username You cannot use the NOT operator with only one search term or phrase. For example, the following search returns no results: NOT username
+	Indicates that the term or phrase after the + is required. Other terms or phrases not preceded by a + are optional. The following example returns all SQL statements that include select: +select distinct SQL statements that also include distinct are ranked higher in the search results.

Wildcards

In advanced mode, you can use the following wildcards:

Wildcard	Description
?	Replaces a single character in the search term. The following example returns SQL statements that contain values or value$:  value?
*	Replaces zero or more characters in the search term. The following example returns SQL statements that refer to any Oracle v$ view: v$*

Wildcards can be used in the following locations:

• You can place wildcards in a single search term, but not in a phrase. A phrase that includes a wildcard (for example "select co?nt") returns no results.

• You can place a wildcard in the middle or at the end of a search term (for example count* or cou?t) A wildcard cannot be placed at the beginning of a term. If a wildcard is the first character of a term (for example, select ?ount), DPA displays the message Unable to parse search request.

Fuzzy searches

Fuzzy searches find terms that are similar in spelling to the specified term. Place a tilde (~) at the end of a single-word term to perform a fuzzy search. For example, the following returns SQL statements that include account, accounts, and count:

account~

Optionally, you can enter a number between 0 and 1 after the tilde to require more similarity than the default fuzzy search. Higher values require more similarity. For example, the following returns SQL statements that include account and accounts, but not count:

account~0.8

Proximity searches

Proximity searches find SQL statements that include all of the specified terms, but only when they are near each other. Place a tilde (~) followed by an integer greater than 0 after a phrase. The integer specifies the maximum number of words separating the terms. For example, the following returns SQL statements with emp and insert separated by no more than 10 words: 

"insert emp"~10

Grouping

Use parentheses to group terms or phrases separated by Boolean operators. For example:

("owner = 'rdsadmin'" OR "owner = 'sys'") AND select

returns SQL statements that include any of the following combinations: 

• select and owner = 'rdsadmin'
• select and owner = 'sys'
• select and both phrases

Escaping special characters

The following special characters must be escaped in advanced mode:

+ - && || ! ( ) { } [ ] ^ " ~ * ? : \

To escape these characters, place a backslash (\) before each special character. For example, to search for SELECT count ( * ), enter:

SELECT count \( \* \)