Oracle REPLACE and REGEXP_REPLACE

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

The REPLACE function is also a value to a value in the replace string. For example, you can use a matching numbers instead of letters every time. The format of the REPLACE is shown as follows:

  1. REPLACE ( char, search_string [, replace_string]) 

If you do not specify the value of the replace_string variable, so when the value of the search_string variable, which could be deleted. The input can be any character data type -- CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB or NCLOB. The following is an example.:

  1. REPLACE('GEORGE', 'GE', 'EG') = EGOREG  
  2. REPLACE('GEORGE', 'GE', NULL) = OR 

If the search string length is not zero, can know the number of search string appears in a series of. First, calculate the length of the source string:

  1. LENGTH('GEORGE') 

Then, calculation of the source string delete search string after the length:

  1. LENGTH(REPLACE('GEORGE', 'GE', NULL)) 

Differential length divided by the length of two times followed by the search string, you can get the number of occurrences of the search string:

  1. select LENGTH('GEORGE')  
  2. - LENGTH(REPLACE('GEORGE', 'GE', NULL))  
  3. /  
  4. LENGTH('GE') AS Counter  
  5. from DUAL;  
  6. COUNTER  
  7. ------- 

The REGEXP_REPLACE function expansion of the REPLACE functions in several aspects. It supports the use of regular expressions in search mode, also support this chapter describes variables, namely position, occurrence and match_parameter, which can only replace some matching value, or insensitive. The REGEXP_REPLACE function syntax is as follows:

  1. REGEXP_REPLACE( source_string, pattern  
  2. [, replace_string  
  3. [, position  
  4. [, occurrence  
  5. [, match_parameter ]  
  6. ]  
  7. ]  
  8. ]  

In addition to replace_string, where all variables are already in the previous chapters are introduced in this paper. Replace_string tells Oracle what to replace source_string and pattern matching section. The variable occurrence is a non negative integer, the number that specifies the operation: if 0, then all the matches are alternative; if you specify a positive number, then Oracle to substitute the N matching.

Consider ADDRESS Phone columns in a table. First of all, find ###-###-#### number format. The format is divided into 3 parts, respectively is 3 digital collections, the back is a collection of the other 3 numbers, and then is a collection of 4 numbers, separated by '-' symbol spaced. By finding the number set in the REGEXP_SUBSTR function call, can find matching with the standard line:

  1. select REGEXP_SUBSTR (Phone,  
  2. '([[:digit:]]{3})-([[:digit:]]{3})-([[:digit:]]{4})'  
  3. ) "REGEXP_SUBSTR"  
  4. from ADDRESS;  
  5. REGEXP_SUBST  
  6. ------------  
  7. 213-555-0223  
  8. 415-555-7530  
  9. 214-555-8383  
  10. 312-555-1166  
  11. 707-555-8900  
  12. 312-555-1414  
  13. 415-555-6842  
  14. 415-555-2178  
  15. 415-555-7387  
  16. 415-555-7512  
  17. 415-555-6252  
  18. 617-555-0125  
  19. 603-555-2242  
  20. 202-555-1414  
  21. 718-555-1638  
  22. 214-555-8383  
  23. 503-555-7491 

Now, the use of REGEXP_REPLACE digital 3 placed in parentheses, and omit the first '-' symbol. Therefore, we will have first digit set called , second data sets are called , third data sets are called .

  1. select REGEXP_REPLACE (Phone,  
  2. '([[:digit:]]{3})-([[:digit:]]{3})-([[:digit:]]{4})'  
  3. , '(\1) \2-\3'  
  4. ) "REGEXP_REPLACE"  
  5. from ADDRESS;  
  6. REGEXP_REPLACE  
  7. ------------------------------------------  
  8. (213) 555-0223  
  9. (415) 555-7530  
  10. (214) 555-8383  
  11. (312) 555-1166  
  12. (707) 555-8900  
  13. (312) 555-1414  
  14. (415) 555-6842  
  15. (415) 555-2178  
  16. (415) 555-7387  
  17. (415) 555-7512  
  18. (415) 555-6252  
  19. (617) 555-0125  
  20. (603) 555-2242  
  21. (202) 555-1414  
  22. (718) 555-1638  
  23. (214) 555-8383  
  24. (503) 555-7491 

The output shows the REGEXP_REPLACE result of the function call, the number in parentheses, the first is to remove the '-'.

In order to explain the working principle of occurrenc variables, the REGEXP_REPLACE function calls the following is used to replace the second periods in the phone number'5':

 

  1. select REGEXP_REPLACE (Phone,  
  2. '5', '.',  
  3. 1, 2  
  4. ) "REGEXP_REPLACE"  
  5. from ADDRESS;  
  6. REGEXP_REPLACE  
  7. ------------------------------------------  
  8. 213-5.5-0223  
  9. 415-.55-7530  
  10. 214-5.5-8383  
  11. 312-5.5-1166  
  12. 707-5.5-8900  
  13. 312-5.5-1414  
  14. 415-.55-6842  
  15. 415-.55-2178  
  16. 415-.55-7387  
  17. 415-.55-7512  
  18. 415-.55-6252  
  19. 617-5.5-0125  
  20. 603-5.5-2242  
  21. 202-5.5-1414  
  22. 718-5.5-1638  
  23. 214-5.5-8383  
  24. 503-.55-7491 

Can further modify the query, excluding digital may match the first 3 (a starting position of 4), and alternative fourth times:

 

  1. select REGEXP_REPLACE (Phone,  
  2. '5', '.',  
  3. 4, 4  
  4. ) "REGEXP_REPLACE"  
  5. from ADDRESS;  
  6. REGEXP_REPLACE  
  7. ------------------------------------------  
  8. 213-555-0223  
  9. 415-555-7.30  
  10. 214-555-8383  
  11. 312-555-1166  
  12. 707-555-8900  
  13. 312-555-1414  
  14. 415-555-6842  
  15. 415-555-2178  
  16. 415-555-7387  
  17. 415-555-7.12  
  18. 415-555-62.2  
  19. 617-555-012.  
  20. 603-555-2242  
  21. 202-555-1414  
  22. 718-555-1638  
  23. 214-555-8383  
  24. 503-555-7491 

Through the use of REGEXP_INSTR in the where clause, you can restrict the rows returned. In this case, display only those containing at least 4 '5' row (from the beginning of the fourth characters). Because the search pattern is not complicated, so here you can also use the INSTR function.

  1. select REGEXP_REPLACE (Phone,  
  2. '5', '.',  
  3. 4, 4  
  4. ) "REGEXP_REPLACE"  
  5. from ADDRESS  
  6. where REGEXP_INSTR(Phone, '5',4,4) > 0;  
  7. 415-555-7.30  
  8. 415-555-7.12  
  9. 415-555-62.2  
  10. 617-555-012. 

You can use this feature to search for alternative values, which combine multiple search criteria in a single query. In the example below, can replace 5 or 2; '5' and '2' frequency are credited to occurrence statistics:

  1. select REGEXP_REPLACE (Phone,  
  2. '(5|2)', '.',  
  3. 4, 4  
  4. ) "REGEXP_REPLACE"  
  5. from ADDRESS  
  6. where REGEXP_INSTR(Phone, '(5|2)',4,4) > 0;  
  7. REGEXP_REPLACE  
  8. ------------------------------------------  
  9. 213-555-0.23  
  10. 415-555-7.30  
  11. 415-555-684.  
  12. 415-555-.178  
  13. 415-555-7.12  
  14. 415-555-6.52  
  15. 617-555-01.5  
  16. 603-555-.242 

Due to the example of the '|' symbol is the operator, an optional therefore, matching two specified values in any one returns a row. For more operators support regular expressions, see table 8-1.

 

Come from

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Posted by Daphne at December 02, 2013 - 9:55 PM