Oracle Regexp_like Introduction and examples

Source: Internet
Author: User
Tags lowercase posix

Oracle Regexp_like Introduction and examples

Studied: http://www.cnblogs.com/einyboy/archive/2012/08/01/2617606.html

There are four main functions in Oracle that support regular expressions: 1,regexp_like: Similar to the function of like 2,regexp_instr: similar to the function of INSTR 3,regexp_substr: similar to SUBSTR function 4, Regexp_replace: Similar to replace's functionality they are used in the same way as Oracle SQL functions like, INSTR, SUBSTR, and replace usages, but they use POSIX regular expressions instead of old percent (%) and wildcard characters (_ Characters The POSIX regular expression is made up of the standard metacharacters (metacharacters): ' ^ ' matches the starting position of the input string and is used in the 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, then $ also matches ' \ 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]+) $ ' represents 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,} ' indicates at least m times. \num matches num, where num is a positive integer. A reference to the obtained match. Character families: [[: 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 \ Escape character () for various operators (?:), (? =), [] parentheses and brackets *, +,?, {n}, {n,}, {n,m} qualifier ^, $, anymetacharacter position and order | */--CREATE TABLE Q (ID varchar (4), Value varchar (10));--Data insertion insert INTO FZQ VALues (' 1 ', ' 1234560 '); insert into FZQ values (' 2 ', ' 1234560 '); 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 ', ' 123 45 ' INSERT into FZQ values (' 8 ', ' ADC de '), insert to FZQ values (' 9 ', ' adc,.de '), insert into fzq values (' Ten ', ' 1 B '); Insert int o fzq values (' Ten ', ' Abcbvbnb '), insert into FZQ values (' One ', ' 11114560 '), insert into FZQ values (' One ', ' 11124560 ');--regexp _like--queries the record in value starting with 1 in 60 and the length is 7-bit select * from FZQ where the value like ' 1____60 '; select * from Fzq where regexp_like (value, ' 1 .... 60 ');--query value with a record ending in 1 with 60 and a length of 7 bits and all numbers. -Using like is not a good implementation. SELECT * from Fzq where regexp_like (value, ' 1[0-9]{4}60 ');--can also be implemented in this way, using a character set. SELECT * from Fzq where regexp_like (value, ' 1[[:d igit:]]{4}60 ');--query for records in value that are not pure numbers select * from Fzq where not regexp_like ( Value, ' ^[[:d igit:]]+$ ');--queries for records that do not contain any numbers in value. SELECT * from Fzq where regexp_like (value, ' ^[^[:d igit:]]+$ ');--queries for records beginning with 12 or 1b. Case-insensitive. SELECT * From Fzq where regexp_like (value, ' ^1[2b] ', ' I ');--queries for records beginning with 12 or 1b. Case-sensitive. SELECT * from Fzq where regexp_like (value, ' ^1[2b] ');--The query data contains a blank record. SELECT * from Fzq where regexp_like (value, ' [[: Space:]] ');--query all records that contain lowercase letters or numbers. SELECT * from Fzq where regexp_like (value, ' ^ ([a-z]+|[ 0-9]+) $ ');--Queries any record that contains punctuation. SELECT * from Fzq where regexp_like (value, ' [[:p UNCT:]] '); example: Determine if the name is empty, less than two characters, contains a number and the letter create or Replacefunction CheckName (NameStr in VARCHAR2)   Return integerasbegin--conforms to return 1, does not conform to return 0 if (NameStr is null or length (NAMESTR) <2) and 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;

Oracle Regexp_like Introduction and examples

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.