ORACLE正则 博客分类： oracle正则 oracle正则

oracle中使用正则匹配一列都是数字，或不是数字
NOT REGEXP_LIKE(description,'[[:digit]]'): Matching for a non-digit

SELECT * FROM dual  
WHERE regexp_like('1234', '^[[:digit:]]+$');

 SELECT *  FROM dual  
WHERE not regexp_like('1234', '^[[:digit:]]+$');

oracle offial Examples

The following query returns the first and last names for those employees with a first name of Steven or Stephen (where first_name begins with Ste and ends with en and in between is either v or ph):

SELECT first_name, last_name
FROM employees
WHERE REGEXP_LIKE (first_name, '^Ste(v|ph)en$');

FIRST_NAME           LAST_NAME
-------------------- -------------------------
Steven               King
Steven               Markle
Stephen              Stiles

The following query returns the last name for those employees with a double vowel(元音字母) in their last name (where last_name contains two adjacent occurrences of either a, e, i, o, or u, regardless of case):

SELECT last_name
FROM employees
WHERE REGEXP_LIKE (last_name, '([aeiou])\1', 'i');

LAST_NAME
-------------------------
De Haan
Greenberg
Khoo
Gee
Greene
Lee
Bloom
Feeney

Character Class Syntax Meaning

[:alnum:] All alphanumeric characters 
[:alpha:] All alphabetic characters 
[:blank:] All blank space characters. 
[:cntrl:] All control characters (nonprinting) 
[:digit:] All numeric digits 
[:graph:] All [:punct:], [:upper:], [:lower:], and [:digit:] characters. 
[:lower:] All lowercase alphabetic characters 
[:print:] All printable characters 
[:punct:] All punctuation characters 
[:space:] All space characters (nonprinting) 
[:upper:] All uppercase alphabetic characters 
[:xdigit:] All valid hexadecimal characters