| Oracle® OLAP DML Reference 10g Release 2 (10.2) Part Number B14346-02 |
|
|
View PDF |
| Oracle® OLAP DML Reference 10g Release 2 (10.2) Part Number B14346-02 |
|
|
View PDF |
The LIKEESCAPE option lets you specify an escape character for the LIKE operator.
Data type
ID
Syntax
LIKEESCAPE = char
Arguments
A text expression that specifies the character to use as an escape character in a LIKE text comparison. The default is no escape character.
The LIKE escape character affects the LISTNAMES program, which accepts a LIKE argument that it uses in a LIKE text comparison.
Notes
Using the Escape Character
The LIKE escape character lets you find text expressions that contain the LIKE operator wildcard characters, which are an underscore (_), which matches any single character, and a percent character (%), which matches any string of zero or more characters.
To include an underscore or percent character in a text comparison, first specify an escape character with the LIKEESCAPE option. Then, in your LIKE expression, precede the underscore or percent character with the LIKEESCAPE character you specified.
You might want to avoid using a backslash (\) as the LIKE escape character, because the backslash is the standard OLAP DML escape character. You would therefore need to have two backslashes to indicate that LIKEESCAPE should treat the second backslash as a literal character.
Examples
Example 17-41 Using an Escape Character with the LIKE Operator
This example demonstrates how to specify an escape character and how to use it with the LIKE operator.
Suppose you have a variable named prodstat that contains the following text values.
DEFINE prodstat TEXT <product> prodstat(product 'Tents') = - 'What are the results of the fabric testing?' prodstat(product 'Canoes') = - 'How has the flooding affected distribution?' prodstat(product 'Racquets') = - 'The best-selling model is Whack_it!' prodstat(product 'Sportswear') = - '90% of the stock is ready to ship.' prodstat(product 'Footwear') = - 'When are the new styles going to be ready?'
Suppose you have the following program, named findeschar, to find certain characters in the text contained in the cells of the prodstat variable. The program uses the LIKE operator.
ARGUMENT findstring TEXT FOR product IF prodstat LIKE findstring THEN SHOW JOINCHARS(product ' - ' prodstat)
Before the program can find a text value that contains a percent character (%) or an underscore (_), you must specify an escape character by using the LIKEESCAPE option. Suppose you want to use a question mark (?) as the escape character. Before you set the escape character to a question mark, the following statement finds text that contains a question mark.
CALL findeschar('%?%') "Find any text that contains a question mark.
The preceding statement produces the following output.
Tents - What are the results of the fabric testing? Canoes - How has the flooding affected distribution? Footwear - When are the new styles going to be ready?
The following statements specify the question mark (?) as the escape character and then call the FINDESCHAR program.
LIKEESCAPE = '?'
CALL findeschar('%?%') "Find any text that ends with a percent character.
The preceding statement does not find any text because none of the text values in prodstat ends in a percent character. To find any text that contains a percent character, the following statement adds another wildcard character. LIKEESCAPE interprets the first percent character as the wildcard that matches zero or more characters, the second percent character as the literal percent character (%) because it is preceded by the question mark escape character, and the third percent character as another wildcard character. The result is that LIKEESCAPE looks for a percent character preceded by and followed by zero or more characters.
CALL findeschar('%?%%') "Find any text that contains a percent character.
The preceding statement produces the following output.
Sportswear - 90% of the stock is ready to ship.
The following statement finds text that contains an underscore.
CALL findeschar('%?%') "Find any text that contains an underscore.
The preceding statement produces the following output.
Racquets - The best-selling model is Whack_it!
The following statement doubles the escape character to find text that contains the escape character.
CALL findeschar('%??%') "Find any text that contains a question mark.
The preceding statement produces the following output.
Tents - What are the results of the fabric testing? Canoes - How has the flooding affected distribution? Footwear - When are the new styles going to be ready?
Example 17-42 Using an Escape Character with the LISTNAMES Program
This example demonstrates how to find the name of an object that contains a LIKE argument wildcard character. These following statements use the LIKEESCAPE option to specify an escape character, define a couple of object names that contain an underscore, and then list the dimensions whose names include an underscore.
LIKEESCAPE = '?' DEFINE my_textdim DIMENSION TEXT DEFINE my_intdim DIMENSION INTEGER LISTNAMES DIMENSION LIKE '%?%'
The preceding statement produces the following output.
3 DIMENSIONs ---------------- MY_INTDIM MY_TEXTDIM _DE_LANGDIM
