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

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

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

Replace Newlines with Commas

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

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

Method 2: Notepad++

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

Method 3: Microsoft Word

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

Method 4: Sed command for Linux

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

Find IP Addresses (IPv4)

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)

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

Add Quotes Around Array Elements

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)

In LibreOffice

[^\u0000-\u0080]+

Find Chinese Characters in Google Sheets

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

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

Find Non-ASCII Characters in Google Sheets

Extract non-ASCII characters (such as Chinese, Japanese, emoji, etc.) from cell A2

=IF(ISERROR(REGEXEXTRACT(A2, "[^\x00-\x80]+")), "", REGEXEXTRACT(A2, "[^\x00-\x80]+"))

Explanation of regular expression [^\x00-\x80]+

• [\x00-\x80]: Represents the ASCII character range (character codes 0-128). (1) Standard ASCII range: 0-127 (0x00-0x7F aka * [\x00-\x7F]) (2) Character 128 ((0x80) is actually the first character in the extended ASCII range, not part of the original ASCII standard.
• [^...]: Means "not" these characters
• +: Means one or more

Overall meaning: Matches one or more non-ASCII characters

Find Chinese Characters in MySQL

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
• "龯" has Unicode code point U+9FEF
• 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

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

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

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

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

• Find: [^\s]+

Convert Symbol-Separated Text to Line-by-Line Display

Example:

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

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

Replace Multiple Spaces with Tab Characters

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

Remove Leading Whitespace

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

Remove Trailing Whitespace

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

Remove Both Leading and Trailing Whitespace

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

Text Editors Supporting Regular Expressions

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

Syntax Reference

• 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

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

• 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)

Further Reading

• Regular-Expressions.info - Regex Tutorial, Examples and Reference
• Unicode character properties documentation
• Platform-specific regular expression documentation

Data factory flow

• 🔍 Data Collection: 1. How to extract content from websites
• 🧹 Data Cleaning: 2. Data cleaning, 3. Regular expression
• ⚙️ Data Processing: 4. Json encode and decode, 5. Convert between date and unix timestamp, 6. MySQL commands
• 📊 Data Analysis: 7. Data exploration
• 📤 Data Output: 8. Comparison of common data file formats, 9. Export MySQL query to Excel file, 10. Microsoft Excel
• 🔧 Troubleshooting: 11. Troubleshooting of MySQL errors

[[Category: Revised with LLMs]