2.17.2 Search using a regular expression
You can use a regular expression to search text data. A regular expression is a means to represent complex search conditions in a single, short conditional expression. For example, you can search for the character string ABC followed by five numbers.
The following figure shows an example of a search using a regular expression.
|
|
![[Figure]](GRAPHICS/F0000089.GIF)
To perform a search using a regular expression, specify the LIKE_REGEX predicate. For details about the LIKE_REGEX predicate, see LIKE_REGEX predicate in Predicates in Constituent Elements in the manual HADB SQL Reference.
Note that when you use a regular expression to search text data, you can reduce the number of pages to be loaded by using a text index. This improves table retrieval performance.
- Note
-
You can use POSIX-based regular expressions (extended regular expressions compliant with POSIX1003.2). Note the following exceptions:
-
The collation element ([. .]) and character equivalence class ([= =]) cannot be used in a character list.
-
The back reference (\n) (n is an integer in the range from 1 to 9) cannot be used.
-
To use a special character as a literal, you must always add an escape character. You must also add an escape character when using a right square bracket (]) as a literal in a character list or using a caret (^) as a literal rather than to mean the beginning of a string.
-
Some operators for Perl extensions can be used. For details about the representations that can be used, see the description of character class in LIKE_REGEX predicate in Predicates in Constituent Elements in the manual HADB SQL Reference.
-
- Important
-
-
You can use a regular expression to create, for example, a representation that matches multiple characters or means some repetitions. Note that, depending on the regular expression that you specify, column data might be evaluated multiple times. Therefore, if the length of column data is long, a search using a regular expression might consume a large amount of memory. Also, the search might require a long time.
-
If you specify a large number as a repetition factor or specify nested repetition factors in succession, the search might consume a large amount of memory. Also, the search might require a long time.
-