Oracle regexp_like knowledge and Examples

Oracle regexp_likeThe knowledge and examples are the main content we will introduce in this article. First, let's take a look at the Oracle SupportRegular ExpressionMainly include the following four functions:

1. REGEXP_LIKE: similar to LIKE.

2. REGEXP_INSTR: similar to INSTR.

3. REGEXP_SUBSTR: similar to SUBSTR.

4. REGEXP_REPLACE: similar to REPLACE.

They are used in the same way as Oracle SQL functions LIKE, INSTR, SUBSTR, and REPLACE, but they use POSIX Regular Expressions instead of the old percent signs %) and wildcard characters.

POSIX regular expressions are composed of standard metacharacters. '$' matches the end position of the input string. If the Multiline attribute of the RegExp object is set, $ also matches '\ n' or' \ R '. '? 'Match the previous subexpression zero or once. '*' Matches the previous subexpression zero or multiple times.

'|' Indicates an option between the two items. Example '^ ([a-z] + | [0-9] +) $' indicates the combination of all lowercase letters or numbers '() 'indicates the start and end positions of a subexpression. '{M, n}' indicates the exact number of occurrences. m = <number of occurrences <= n, '{m}' indicates m occurrences, '{m ,} 'indicates that at least m occurs.

\ Num matches num, where num is a positive integer. References to the obtained matching. [[: Alpha:] any letter.

[[: Digit:] any number.

[[: Alnum:] Any letter or number.

[[: Space:] any white characters.

[[: Upper:] Any uppercase letter.

[[: Lower:] Any lowercase letter.

[[: Punct:] Any punctuation marks.

[[: Xdigit:] Any hexadecimal number, which is equivalent to [0-9a-fA-F]. \ Escape Character *, + ,?, {N}, {n ,}, {n, m} qualifier ^, $, anymetacharacter location and order.

 
 

  
  
Create table fzq id varchar (4 ),
  
  
Value varchar (10) -- data insertion
  
  
Insert into fzq values
  
  
('1', '123 ');
  
  
Insert into fzq values
  
  
('2', '123 ');
  
  
Insert into fzq values
  
  
('3', '1b3b560 ');
  
  
Insert into fzq values
  
  
('4', 'abc ');
  
  
Insert into fzq values
  
  
('5', 'abcde ');
  
  
Insert into fzq values
  
  
('6', 'adreasx ');
  
  
Insert into fzq values
  
  
('7', '2014, 123 45 ');
  
  
Insert into fzq values
  
  
('8', 'adc de ');
  
  
Insert into fzq values
  
  
('9', 'adc,. de ');
  
  
Insert into fzq values
  
  
('10', '1b ');
  
  
Insert into fzq values
  
  
('10', 'abcbvbnb ');
  
  
Insert into fzq values
  
  
('11', '123 ');
  
  
Insert into fzq values
  
  
('11', '123 ');
 
 

-- Regexp_like

-- Query records whose names start with 60 and end with 1 and whose length is 7 characters.

 
 

  
  
select * from fzq where value like '1____60';  
  
  
select * from fzq where regexp_like(value,'1....60'); 
 
 

-- Query records whose names start with 60 and end with 1 and whose length is 7 digits and all are numbers.

-- Using like is not a good implementation.

 
 

  
  
select * from fzq where regexp_like(value,'1[0-9]{4}60');   
  
  
select * from fzq where regexp_like(value,'1[[:digit:]]{4}60'); 
 
 

-- Query records whose values are not pure numbers

Select * from fzq where not regexp_like (value, '^ [[: digit:] + $ ');

-- Query records whose values do not contain any numbers.

Select * from fzq where regexp_like (value, '^ [^ [: digit:] + $ ');

-- Query records starting with 12 or 1b. The records are case insensitive.

Select * from fzq where regexp_like (value, '^ 1 [2b]', 'I ');

-- Query records starting with 12 or 1b. case sensitive.

 
 

  
  
select * from fzq where regexp_like(value,'^1[2B]'); select * from fzq where regexp_like(value,'[[:space:]]');   
  
  
select * from fzq where regexp_like(value,'^([a-z]+|[0-9]+)$'); select * from fzq where regexp_like(value,'[[:punct:]]'); 
 
 

Example: judge whether the name is null. It contains less than two characters, including numbers and letters.

 
 

  
  
Create or replace
  
  
FUNCTION CheckName (NameStr in VARCHAR2) RETURN integer
  
  
As
  
  
BEGIN
  
  
-- Returns 1 If yes, and returns 0 if no
  
  
If (NameStr is null or length (NameStr) <2) then
  
  
Return 0;
  
  
Else
  
  
If (NameStr like '% not named %') then
  
  
RETURN 0;
  
  
End if;
  
  
If regexp_like (NameStr, '^ ([a-z] + | [0-9] + | [A-Z] +) $') then
  
  
Return 0;
  
  
End if;
  
  
Return 1;
  
  
End if;
  
  
END CheckName;
 
 

This section describes the knowledge of Oracle regexp_like and examples. We hope this introduction will be helpful to you.