Plsql NOTE--------like with escape character

Sql> CREATE TABLE Test  2  (ID integer,  3  name VARCHAR2 (90)); Table created. sql> INSERT into test values (+, ' AASSDD '); 1 row created. sql> INSERT into test values (120,null); 1 row created. sql> INSERT into test values (, ' aa_see '); 1 row created. Sql> Select ID from the test where name like ' aa/_% ' escape '/';    ID----------       110sql> Select ID from the test where name like ' aa% ';    ID----------       110sql> select ID from test where name is like ' AASSDD ';    ID----------       100sql> insert INTO test values (123, ' dfhi&sdklv '); Enter value for SDKLV: &hkdfjold   1:insert to test values (123, ' dfhi&sdklv ') new   1:insert into test Val UES (123, ' DFHI&HKDFJ ') 1 row created.

Note:

The default escape character in Plsql is ', so ' the word Fu Ching is expressed as ' ';

% 0 or more characters
_ Single any character (underscore)
\ special Characters

Escape is used to customize the escape character.

=============================================================================================================== ===================================================================

There are four main functions that support regular expressions above oracle10g:
1,regexp_like: Similar to the function of like
2,REGEXP_INSTR: Similar to the function of INSTR
3,REGEXP_SUBSTR: Similar to the function of SUBSTR
4,regexp_replace: Similar to the function of REPLACE
POSIX Regular expressions are composed of standard metacharacters (metacharacters):
' ^ ' matches the starting position of the input string, which is used in a square bracket expression, which indicates that the character set is not accepted.
' $ ' matches the end position of the input string. If the Multiline property of the RegExp object is set, the $ is also
With ' \ n ' or ' \ R '.
'. ' matches any single character except for a newline character.
'? ' matches the preceding subexpression 0 or one time.
' + ' matches the preceding sub-expression one or more times.
' * ' matches the preceding subexpression 0 or more times.
' | ' indicates a choice between the two items. Example ' ^ ([a-z]+|[ 0-9]+) $ ' denotes all lowercase letters or numbers combined into a
String.
' () ' marks the start and end position of a subexpression.
' [] ' marks a bracket expression.
' {m,n} ' an exact occurrence of the frequency range,m=< occurrences <=n, ' {m} ' indicates the presence of M times, ' {m,} ' means at least
Appears m times.
\num matches num, where num is a positive integer. A reference to the obtained match.
Character clusters:
[[: Alpha:]] any letter.
[[:d Igit:]] any number.
[[: Alnum:]] Any letters and numbers.
[[: Space:]] any whitespace character.
[[: Upper:]] any uppercase letters.
[[: Lower:]] any lowercase letter.
[[:p UNCT:]] any punctuation.
[[: Xdigit:]] Any 16 binary number, equivalent to [0-9a-fa-f].
Operator precedence for various operators
\ escape Character
(), (?:), (? =), [] parentheses and square brackets
*, +,?, {n}, {n,}, {n,m} qualifier
^, $, anymetacharacter position and order
|
*/

=============================================================================================================== ==================================================

1,%: Represents any of 0 or more characters. Can match any type and length of the character, in some cases, if Chinese, please use two percent sign (%) .

For example SELECT * from [user] WHERE u_name like '% three '

will be u_name for "Zhang San", "Zhang Cat Three", "three-legged Cat", "Tang Sanzang" and so on Have "three" records all find out.

Also, if you need to find a record of "three" and "cat" in U_name, use the and condition
SELECT * FROM [user] WHERE u_name like '% three ' and u_name like '% cat% '

If using SELECT * from [user] WHERE u_name like '% cat% '
Although can search out "three feet cat", but can not search out the eligible "Zhang Cat three".

2,_: Represents any single character. Matches a single arbitrary character, which is commonly used to restrict the expression's character-length statement:

For example SELECT * from [user] WHERE u_name like ' _ Three _ '
Only find "Tang Sanzang" so u_name for three words and the middle of a word is "three";

Another example is SELECT * from [user] WHERE u_name like ' three __ ';
Just find out "three-legged cat" this name is three words and the first word is "three";

3. Regexp_like Regular Expression function query
--Query the record in FieldName with 1 beginning 60 and the length is 7 bits
SELECT * from Fzq where FieldName like ' 1____60 ';
SELECT * from Fzq where regexp_like (FieldName, ' 1....60 ');
--Query the records in FieldName that end with 1 in the beginning of 60 and the length is 7 bits and all are numbers.
-Using like is not a good implementation.
SELECT * from Fzq where regexp_like (FieldName, ' 1[0-9]{4}60 ');
--This can also be done using a character set.
SELECT * from Fzq where regexp_like (FieldName, ' 1[[:d igit:]]{4}60 ');
--query for records in FieldName that are not pure numbers
SELECT * from Fzq where not regexp_like (FieldName, ' ^[[:d igit:]]+$ ');
--query for records in FieldName that do not contain any numbers.
SELECT * from Fzq where regexp_like (FieldName, ' ^[^[:d igit:]]+$ ');
--query for records beginning with 12 or 1b. Case insensitive.
SELECT * from Fzq where regexp_like (FieldName, ' ^1[2b] ', ' I ');
--query for records beginning with 12 or 1b. Case-sensitive.
SELECT * from Fzq where regexp_like (FieldName, ' ^1[2b] ');
--The query contains blank records in the data.
SELECT * from Fzq where regexp_like (FieldName, ' [[: Space:]] ');
--query all records that contain lowercase letters or numbers.
SELECT * from Fzq where regexp_like (FieldName, ' ^ ([a-z]+|[ 0-9]+) ($ ');
--Query any record that contains punctuation.
SELECT * from Fzq where regexp_like (FieldName, ' [[:p UNCT:] ');

Plsql NOTE--------like with escape character