Regular expression

When processing text files through regular expressions, you can quickly search for or replace strings that match specific rules. Processing is done on a line-by-line basis for string manipulation. Regular expressions are also known as regex, regexp, or pattern matching expressions.

🌐 Switch language: English, 漢字

Need Help? You can use the provided explanatory online tools to try debugging yourself.

Quick Reference Table[edit]

Note: (1) Blue highlighted areas in samples represent text matching the rules, (2) The same text rule can have multiple representations

Text Rule	Sample	Opposite Text Rule	Sample
Any single character (including spaces, but not newline) `.`	What Does the Fox Say? 12 狐狸怎叫 34
Any character (including spaces), appears 1 or 0 times `.?` = `.{0,1}`	What Does the Fox Say? 12 狐狸怎叫 34
Any number of multiple characters (including spaces) `.*` = `.{0,}`	What Does the Fox Say? 12 狐狸怎叫 34
Any number of characters (including spaces), at least 1 occurrence `.+` = `.{1,}`	What Does the Fox Say? 12 狐狸怎叫 34
Any number of spaces or newlines (at least 1 occurrence) `\s+`	What Does the Fox Say? 12 狐狸怎叫 34	Any number of characters (not including spaces or newlines) `[^\s]+` = `[^\s]{1,}` = `[\S]+` = `[^ ]+`	What Does the Fox Say? 12 狐狸怎叫 34
Any number of ASCII characters (including English, numbers and spaces) `[\x00-\x80]+` or `ascii:+`	What Does the Fox Say? 12 狐狸怎叫 34	Non-ASCII, i.e., Chinese characters appearing any number of times `[^\x00-\x80]+`	What Does the Fox Say? 12 狐狸怎叫 34
Any number of uppercase/lowercase English letters, numbers and underscore (_) (not including spaces) `[\w]+` = `[a-zA-Z0-9_]+` PHP with `u` modifier supports Chinese characters	What Does the Fox Say? 12 狐狸怎叫 _34	Any number of characters that are not English letters, numbers and underscore (_) `\W+` = `[^a-zA-Z0-9_]+`
Any number of digits (not including spaces) `[\d]+` = `[0-9]+`	What Does the Fox Say? 12 狐狸怎叫 34	Any number of characters not including digits (including spaces) `[^\d]+` = `[^0-9]+` = `\D+`	What Does the Fox Say? 12 狐狸怎叫 34
Any number of Chinese characters `[\p{Han}]+`	What Does the Fox Say? 12 狐狸怎叫 34	Any number of characters not including Chinese `[^\p{Han}]+`
Lines starting with “狐狸” `^狐狸.*$`	狐狸怎叫 34 What Does the Fox Say? 柴犬怎叫 What Does the shiba inu say?	Lines not starting with “狐狸” `^(?!狐狸).*$`	狐狸怎叫 34 What Does the Fox Say? 柴犬怎叫 What Does the shiba inu say?
Lines ending with “怎叫” `^.*怎叫$`	What Does the Fox Say? 12 狐狸怎叫 34 What Does the shiba inu say? 柴犬怎叫	Lines not ending with “怎叫” `.*(?<!怎叫)$`	What Does the Fox Say? 12 狐狸怎叫 34 What Does the shiba inu say? 柴犬怎叫
Lines containing “狐狸” `^.狐狸.$` or `(狐狸)`	What Does the Fox Say? 12 狐狸怎叫 34 What Does the shiba inu say? 柴犬怎叫	Lines not containing “狐狸” `^((?!狐狸).)*$`	What Does the Fox Say? 12 狐狸怎叫 34 What Does the shiba inu say? 柴犬怎叫
叫.*狐狸	What Does the Fox Say? 12 狐狸怎叫 34 What Does the Fox Say? 12 不叫狐狸 34 What Does the shiba inu say? 柴犬怎叫
叫).*	What Does the Fox Say? 12 狐狸怎叫 34 What Does the shiba inu say? 柴犬怎叫 What Does the shiba inu say? 柴犬怎了	柴犬).)*$	What Does the Fox Say? 12 狐狸怎叫 34 What Does the shiba inu say? 柴犬怎叫 What Does the Husky say? 哈士奇怎叫
Boolean logic NOT: Lines not containing “狐狸” but containing “柴犬” `^((?!狐狸).)(柴犬).$` = `^(柴犬).((?!狐狸).)$` = `(柴犬).((?!狐狸).)`	What Does the Fox Say? 12 狐狸怎叫 34 What Does the shiba inu say? 柴犬怎叫

Regular Expression Online Tools[edit]

Websites for testing regular expression syntax:

RegEx101 - “Online regex tester and debugger: PHP, PCRE, Python, Golang and JavaScript” - Provides syntax explanations
RegExr - Learn, Build, & Test RegEx - Provides syntax explanations
Regexper - Visual explanation of syntax using diagrams
Regulex：JavaScript Regular Expression Visualizer - JavaScript Regular Expression Visualizer - Visual explanation using diagrams
Rubular - A Ruby regular expression editor and tester
PHP Live Regex
Regex Tester and Debugger Online - JavaScript, PCRE, PHP

Common Use Cases[edit]

Replace Newlines with Commas[edit]

Converting email lists into a format usable by email software:

Original:
[email protected]
[email protected]
[email protected]

Convert to:
[email protected],[email protected],[email protected]

Method 1: Sublime Text, EmEditor[edit]

Menu: Search -> Replace
Check “Use Regular Expression”
- Find: \n (newline character)
- Replace with: ,
Click “Replace all”

Method 2: Notepad++[edit]

Menu: Find -> Replace
Search mode: Check “Extended mode” (not “Regular expression”)
- Find: \n
- Replace with: ,
Click “Replace All”

Method 3: Microsoft Word[edit]

Menu: Edit -> Replace
Check extended mode
- Find: ^p (paragraph mark)
- Replace with: ,
Click “Replace All”

Method 4: Sed command for Linux[edit]

sed ':a;N;$!ba;s/\n/; /g' old.filename > new.filename

Find IP Addresses (IPv4)[edit]

For Notepad++ v.5.9.5: - Find: \d\d?\d?\.\d\d?\d?\.\d\d?\d?\.\d\d?\d?

For Sublime Text v. 3.2.21: - Find: (?:\d{1,3}\.){3}\d{1,3}

Remove Black Squares (UNIX Line Endings LF)[edit]

Using Notepad++: 1. Menu: Find -> Replace 2. Search mode: Check “Extended mode” - Find: \n\n (2 LF characters) - Replace with: \r\n (CR and LF)

Add Quotes Around Elements[edit]

Add Quotes Around Array Elements[edit]

Before: Elmo, Emie, Granny Bird, Herry Monster, 喀喀獸
After: 'Elmo', 'Emie', 'Granny Bird', 'Herry Monster', '喀喀獸'

Method 1: PHP

$users = array('Elmo', 'Emie', 'Granny Bird', 'Herry Monster', '喀喀獸');
// Single quotes around each element
$result = implode(",", preg_replace('/^(.*?)$/', "'$1'", $users));
// Double quotes around each element
$result = implode(",", preg_replace('/^(.*?)$/', "\"$1\"", $users));
echo $result;

Method 2: Sublime Text or EmEditor - Find: ([^\s|,]+) - Replace with: '\1' (for single quotes) or "\1" (for double quotes)

Method 3: Notepad++ (Enable “Regular expression” search mode) - Find: ([^\s|,]+) - Replace with: '$1' (for single quotes) or "$1" (for double quotes)

Find Non-ASCII Characters (Chinese/Non-English Text)[edit]

In Google Sheets[edit]

[^\x00-\x80]+

In LibreOffice[edit]

[^\u0000-\u0080]+

Find Chinese Characters in Google Sheets[edit]

Example: If A2 contains any Chinese character, display “Chinese”, otherwise display “English”:

=IF(REGEXMATCH(A2, "[\一-\龥]"), "Chinese", "English")

Find Chinese Characters in MySQL[edit]

Find rows where column_name contains Chinese characters:

SELECT `column_name`
FROM `table_name`
WHERE HEX(`column_name`) REGEXP '^(..)*(E[4-9])';

Query condition used to match records where the column_name field contains only Chinese characters.

SELECT `column_name`
FROM `table_name`
WHERE `column_name` REGEXP '^[一-龯]+$';

Explanation:

[一-龯] - Character set that matches all characters from "一" to "龯" in Unicode
"一" has Unicode code point U+4E00^[1]
"龯" has Unicode code point U+9FEF^[2]
This range U+4E00-U+9FFF already covers over 99% of daily Chinese usage requirements Extension B and later blocks mainly contain ancient Chinese characters, variant characters, etc., which rarely appear in modern texts

Find Non-ASCII Characters in MySQL[edit]

Find rows where column_name is not entirely ASCII characters:

SELECT `column_name`
FROM `table_name`
WHERE `column_name` <> CONVERT(`column_name` USING ASCII)

Find Chinese Characters in PHP[edit]

Exact match:

// Approach 1
if (preg_match('/^[\x{4e00}-\x{9fa5}]+$/u', $string)) {
    echo "All text is Chinese characters" . PHP_EOL;
} else {
    echo "Some text is not Chinese characters" . PHP_EOL;
}

// Approach 2
if (preg_match('/^[\p{Han}]+$/u', $string)) {
    echo "All text is Chinese characters" . PHP_EOL;
} else {
    echo "Some text is not Chinese characters" . PHP_EOL;
}

Partial match:

// Approach 1
$string = '繁體中文-简体中文-English-12345-。，！-.,!-⭐';
$pattern = '/[\p{Han}]+/u';
preg_match_all($pattern, $string, $matches, PREG_OFFSET_CAPTURE);
var_dump($matches);

// Approach 2
$string = '繁體中文-简体中文-English-12345-。，！-.,!-⭐';
$pattern = '/[\x{4e00}-\x{9fa5}]+/u';
preg_match_all($pattern, $string, $matches, PREG_OFFSET_CAPTURE);
var_dump($matches);

Find ASCII Characters in PHP[edit]

Code I:

if (preg_match('/[^\x20-\x7f]/', $keyword) === 0) {
    echo "The keyword is ASCII only";
} else {
    echo "The keyword contains non-ASCII characters (like Chinese, Japanese, etc.)";
}

Code II:

$pattern = '/^[[:ascii:]]+$/i';
$text = "Hello World"; // ASCII only
if (preg_match($pattern, $text)) {
    echo "Pure ASCII characters";
} else {
    echo "Contains non-ASCII characters";
}

Remove Empty Lines[edit]

Original:

Neo
Trinity

Morpheus


Smith
Oracle

After:

Neo
Trinity
Morpheus
Smith
Oracle

Using Sublime Text & EmEditor: - Find: ^[\s\t]*$\n - Replace with: (empty)

Using Notepad++ v7.8.7: - Menu: Edit -> Line Operations -> Remove Empty Lines (Including Blank Lines)

Find Non-Whitespace Text[edit]

Find: [^\s]+

Convert Symbol-Separated Text to Line-by-Line Display[edit]

Example:

Before: 尼歐、莫斐斯、崔妮蒂、史密斯、祭師
After:
尼歐
莫斐斯
崔妮蒂
史密斯
祭師

Using Sublime Text or EmEditor: - Find: ([^、]+)([、]{1}) - Replace with: \1\n

Replace Multiple Spaces with Tab Characters[edit]

Before: aaa bbb ccc After: aaa\tbbb\tccc

Using Sublime Text: - Find: ([^\S\n]+) or ([^\S\r\n]+) or \s\s+ - Replace with: \t

Remove Leading/Trailing Whitespace[edit]

Remove Leading Whitespace[edit]

Find: ^\s+
Replace with: (empty)

Remove Trailing Whitespace[edit]

Find: \s+$
Replace with: (empty)

Remove Both Leading and Trailing Whitespace[edit]

Find: (^\s+|\s+$)
Replace with: (empty)

Text Editors Supporting Regular Expressions[edit]

Various text editors support regular expressions including: - Sublime Text - EmEditor - Notepad++ - Visual Studio Code - Atom - Vim/Neovim

Syntax Reference[edit]

Newline character: \r\n (for Notepad++: Extended mode & Regular expression mode)
Tab character: \t (for Notepad++: Extended mode)
Digits: \d (for Notepad++: Regular expression mode only)
Non-whitespace: \S - Does not include half-width spaces and full-width spaces

Troubleshooting Regular Expressions[edit]

Tips: 1. Use online tools like regex101 to understand your syntax 2. Test with small data: Prepare small file data to verify syntax 3. Highlight or output matched text for debugging 4. Simplify the syntax when encountering issues 5. Try alternative syntax due to compatibility issues (e.g., \d to [0-9]+)

Alternative Solutions[edit]

Use Tab-separated data that can be easily pasted into Google Sheets or MS Excel
Copy multiple rows and paste between different applications (compatibility varies)