search
Search
Login
Math ML
Map of Data Science
Join our weekly DS/ML newsletter
menu
menu search toc more_vert
Robocat
Guest 0reps
Thanks for the thanks!
close
Comments
Log in or sign up
Cancel
Post
account_circle
Profile
exit_to_app
Sign out
help Ask a question
Share on Twitter
search
keyboard_voice
close
Searching Tips
Search for a recipe:
"Creating a table in MySQL"
Search for an API documentation: "@append"
Search for code: "!dataframe"
Apply a tag filter: "#python"
Useful Shortcuts
/ to open search panel
Esc to close search panel
to navigate between search results
d to clear all current filters
Enter to expand content preview
icon_star
Doc Search
icon_star
Code Search Beta
SORRY NOTHING FOUND!
mic
Start speaking...
Voice search is only supported in Safari and Chrome.
Navigate to
A
A
brightness_medium
share
arrow_backShare
check_circle
Mark as learned
thumb_up
0
thumb_down
0
chat_bubble_outline
0
auto_stories new
settings

MySQL | REGEXP_REPLACE method

Database
chevron_right
MySQL
chevron_right
Documentation
chevron_right
Functions
chevron_right
Regular Expression
schedule Jul 1, 2022
Last updated
local_offer MySQL
Tags

MySQL's REGEXP_REPLACE(~) method returns the input string expr with all occurrences that match the regular expression pat replaced by the new substring repl.

Parameters

1. expr | string

The string to perform regular expression matching and replace on.

2. pat | regular expression

The regular expression pattern to be used for matching.

3. repl | string

The substring to replace the occurrences in expr that match the regular expression pattern.

4. pos | integer | optional

The position in expr at which to start the search. Defaults to 1.

5. occurrence | number | optional

Which occurrence of a match to replace. Defaults to 0 (all occurrences).

6. match_type | string | optional

Specifies how to perform matching. Multiple characters may be specified, however, if there is a contradiction between the provided match_types the match_type on the right take precedence.

match_type

Meaning

c

Case sensitive matching.

i

Case-insensitive matching.

m

Multiple-line mode. Recognize line terminators within the string. The default behavior is to match line terminators only at the start and end of the string expression.

n

The . character matches line terminators. Default is for . matching to stop at the end of a line.

u

Unix-only line endings. Only the newline character is recognized as a line ending by the ., ^, and $ match operators.

Examples

Consider the following table about some students:

student_id

fname

lname

day_enrolled

age

username

1

Sky

Towner

2015-12-03

17

stowner1

2

Ben

Davis

2016-04-20

19

bdavis2

3

Travis

Apple

2018-08-14

18

tapple3

4

Arthur

David

2016-04-01

16

adavid4

5

Benjamin

Town

2014-01-01

17

btown5

The above table can be created using the code here.

Basic usage

To replace all occurrences of [ae] regular expression match with '%' in student last names:

SELECT lname, REGEXP_REPLACE(lname, '[ae]', '%')
FROM students;
+--------+----------------------------------+
| lname | REGEXP_REPLACE(lname,'[ae]','%') |
+--------+----------------------------------+
| Towner | Town%r |
| Davis | D%vis |
| Apple | %ppl% |
| David | D%vid |
| Town | Town |
+--------+----------------------------------+

Notice how all a and e characters in student last names have now been replaced with %.

Position

To only start the regular expression matching from position 4:

SELECT REGEXP_REPLACE('abcdefghi', '[a-z]', 'X', 4, 0, 'c');
+------------------------------------------------------+
| REGEXP_REPLACE('abcdefghi', '[a-z]', 'X', 4, 0, 'c') |
+------------------------------------------------------+
| abcXXXXXX |
+------------------------------------------------------+

Notice how we only start replacing from position 4 (occupied by d in the original string 'abcdefghi').

Occurence

To replace the second occurrence of match:

SELECT REGEXP_REPLACE('abc def ghi', '[a-z]', 'X', 1, 2, 'c');
+--------------------------------------------------------+
| REGEXP_REPLACE('abc def ghi', '[a-z]', 'X', 1, 2, 'c') |
+--------------------------------------------------------+
| aXc def ghi |
+--------------------------------------------------------+

Notice how we only replace the second occurrence of regular expression match ('b' in the original string 'abc def ghi').

Match type

To perform a case sensitive match:

SELECT REGEXP_REPLACE('abc DEF ghi', '[a-z]+', 'X', 1, 0, 'c');
+---------------------------------------------------------+
| REGEXP_REPLACE('abc DEF ghi', '[a-z]+', 'X', 1, 0, 'c') |
+---------------------------------------------------------+
| X DEF X |
+---------------------------------------------------------+

Notice how 'DEF' is not replaced with 'X' as we specified match_type of 'c'. If we had specified a match_type of 'i' (case insensitive) we would have returned 'X X X'.

mail
Join our newsletter for updates on new DS/ML comprehensive guides (spam-free)
robocat
Published by Arthur Yanagisawa
Edited by 0 others
Did you find this page useful?
thumb_up
thumb_down
thumb_up
0
thumb_down
0
chat_bubble_outline
0
settings
Enjoy our search
Hit / to insta-search docs and recipes!