15,049
edits
Regular expression: Difference between revisions
Jump to navigation
Jump to search
m
no edit summary
mNo edit summary |
|||
| (7 intermediate revisions by the same user not shown) | |||
| Line 3: | Line 3: | ||
{{LanguageSwitcher | content = [[Regular expression | English]], [[Regular expression in Mandarin|漢字]]}} | {{LanguageSwitcher | content = [[Regular expression | English]], [[Regular expression in Mandarin|漢字]]}} | ||
{{Raise hand | text = '''Need Help?''' You can use the provided explanatory [[#regular-expression-online-tools|online tools]] to try debugging yourself. }} | |||
== Quick Reference Table == | == Quick Reference Table == | ||
| Line 93: | Line 93: | ||
|} | |} | ||
== Regular Expression Online Tools == | == Regular Expression Online Tools == | ||
| Line 106: | Line 106: | ||
== Common Use Cases == | == Common Use Cases == | ||
=== Replace Newlines with Commas === | === Replace Newlines with Commas === | ||
| Line 122: | Line 121: | ||
Convert to: | Convert to: | ||
==== Method 1: Sublime Text, EmEditor ==== | ==== Method 1: Sublime Text, EmEditor ==== | ||
| Line 131: | Line 130: | ||
# Click “Replace all” | # Click “Replace all” | ||
==== Method 2: Notepad++ ==== | ==== Method 2: Notepad++ ==== | ||
| Line 140: | Line 139: | ||
# Click “Replace All” | # Click “Replace All” | ||
==== Method 3: Microsoft Word ==== | ==== Method 3: Microsoft Word ==== | ||
| Line 149: | Line 148: | ||
# Click “Replace All” | # Click “Replace All” | ||
==== Method 4: Sed command for Linux ==== | ==== Method 4: Sed command for Linux ==== | ||
<syntaxhighlight lang="bash">sed ':a;N;$!ba;s/\n/; /g' old.filename > new.filename</syntaxhighlight> | <syntaxhighlight lang="bash">sed ':a;N;$!ba;s/\n/; /g' old.filename > new.filename</syntaxhighlight> | ||
=== Find IP Addresses (IPv4) === | === Find IP Addresses (IPv4) === | ||
| Line 160: | Line 158: | ||
For Sublime Text v. 3.2.21: - Find: <code>(?:\d{1,3}\.){3}\d{1,3}</code> | For Sublime Text v. 3.2.21: - Find: <code>(?:\d{1,3}\.){3}\d{1,3}</code> | ||
=== Remove Black Squares (UNIX Line Endings LF) === | === Remove Black Squares (UNIX Line Endings LF) === | ||
Using Notepad++: 1. Menu: Find -> Replace 2. Search mode: Check “Extended mode” - Find: <code>\n\n</code> (2 LF characters) - Replace with: <code>\r\n</code> (CR and LF) | Using Notepad++: 1. Menu: Find -> Replace 2. Search mode: Check “Extended mode” - Find: <code>\n\n</code> (2 LF characters) - Replace with: <code>\r\n</code> (CR and LF) | ||
=== Add Quotes Around Elements === | === Add Quotes Around Elements === | ||
==== Add Quotes Around Array Elements ==== | ==== Add Quotes Around Array Elements ==== | ||
| Line 185: | Line 180: | ||
'''Method 3: Notepad++''' (Enable “Regular expression” search mode) - Find: <code>([^\s|,]+)</code> - Replace with: <code>'$1'</code> (for single quotes) or <code>"$1"</code> (for double quotes) | '''Method 3: Notepad++''' (Enable “Regular expression” search mode) - Find: <code>([^\s|,]+)</code> - Replace with: <code>'$1'</code> (for single quotes) or <code>"$1"</code> (for double quotes) | ||
=== Find Non-ASCII Characters (Chinese/Non-English Text) === | === Find Non-ASCII Characters (Chinese/Non-English Text) === | ||
==== In LibreOffice ==== | ==== In LibreOffice ==== | ||
<pre>[^\u0000-\u0080]+</pre> | <pre>[^\u0000-\u0080]+</pre> | ||
==== Find Chinese Characters in Google Sheets ==== | ==== Find Chinese Characters in Google Sheets ==== | ||
Example: If A2 contains any Chinese character, display “Chinese”, otherwise display “English”: | Example: If cell {{kbd | key=A2}} contains any Chinese character, display “Chinese”, otherwise display “English”: | ||
<pre>=IF(REGEXMATCH(A2, "[\一-\龥]"), "Chinese", "English")</pre> | <pre>=IF(REGEXMATCH(A2, "[\一-\龥]"), "Chinese", "English")</pre> | ||
< | |||
==== Find Non-ASCII Characters in Google Sheets ==== | |||
Extract non-ASCII characters (such as Chinese, Japanese, emoji, etc.) from cell {{kbd | key=A2}} | |||
<pre> | |||
=IF(ISERROR(REGEXEXTRACT(A2, "[^\x00-\x80]+")), "", REGEXEXTRACT(A2, "[^\x00-\x80]+")) | |||
</pre> | |||
Explanation of regular expression {{kbd | key=<nowiki>[^\x00-\x80]+</nowiki>}} | |||
* {{kbd | key=<nowiki>[\x00-\x80]</nowiki>}}: Represents the ASCII character range (character codes 0-128). (1) Standard ASCII range: 0-127 ({{kbd | key=<nowiki>0x00-0x7F</nowiki>}} aka * {{kbd | key=<nowiki>[\x00-\x7F]</nowiki>}})<ref>[https://www.commfront.com/pages/ascii-chart ASCII Chart – CommFront]</ref> (2) Character 128 (({{kbd | key=<nowiki>0x80</nowiki>}}) is actually the first character in the extended ASCII range, not part of the original ASCII standard.<ref>[https://en.wikipedia.org/wiki/UTF-8 UTF-8 - Wikipedia]</ref><ref>[https://en.wikipedia.org/wiki/Control_character Control character - Wikipedia]</ref> | |||
* {{kbd | key=<nowiki>[^...]</nowiki>}}: Means "not" these characters | |||
* {{kbd | key=<nowiki>+</nowiki>}}: Means one or more | |||
Overall meaning: Matches one or more non-ASCII characters | |||
==== Find Chinese Characters in MySQL ==== | ==== Find Chinese Characters in MySQL ==== | ||
Find rows where <code>column_name</code> contains Chinese characters: | Find rows where <code>column_name</code> contains Chinese characters: | ||
< | <pre lang="sql">SELECT `column_name` | ||
FROM `table_name` | |||
WHERE HEX(`column_name`) REGEXP '^(..)*(E[4-9])';</pre> | |||
Query condition used to match records where the <code>column_name</code> field contains only Chinese characters. | |||
<pre lang="sql">SELECT `column_name` | |||
FROM `table_name` | FROM `table_name` | ||
WHERE | WHERE `column_name` REGEXP '^[一-龯]+$';</pre> | ||
< | |||
Explanation: | |||
* {{kbd | key=<nowiki>[一-龯]</nowiki>}} - Character set that matches all characters from "一" to "龯" in Unicode | |||
* "一" has Unicode code point {{kbd | key=<nowiki>U+4E00</nowiki>}}<ref>[https://www.compart.com/en/unicode/U+4E00 “一” U+4E00 CJK Unified Ideograph-4E00 Unicode Character]</ref> | |||
* "龯" has Unicode code point {{kbd | key=<nowiki>U+9FEF</nowiki>}}<ref>[https://www.compart.com/en/unicode/U+9FAF “龯” U+9FAF CJK Unified Ideograph-9FAF Unicode Character]</ref> | |||
* This range U+4E00-U+9FFF already covers over 99% of daily Chinese usage requirements [https://en.wikipedia.org/wiki/CJK_Unified_Ideographs_Extension_B 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 Non-ASCII Characters in MySQL ==== | ||
| Line 218: | Line 234: | ||
FROM `table_name` | FROM `table_name` | ||
WHERE `column_name` <> CONVERT(`column_name` USING ASCII)</syntaxhighlight> | WHERE `column_name` <> CONVERT(`column_name` USING ASCII)</syntaxhighlight> | ||
==== Find Chinese Characters in PHP ==== | ==== Find Chinese Characters in PHP ==== | ||
| Line 249: | Line 265: | ||
preg_match_all($pattern, $string, $matches, PREG_OFFSET_CAPTURE); | preg_match_all($pattern, $string, $matches, PREG_OFFSET_CAPTURE); | ||
var_dump($matches);</syntaxhighlight> | var_dump($matches);</syntaxhighlight> | ||
=== Find ASCII Characters in PHP === | === Find ASCII Characters in PHP === | ||
| Line 268: | Line 284: | ||
echo "Contains non-ASCII characters"; | echo "Contains non-ASCII characters"; | ||
}</syntaxhighlight> | }</syntaxhighlight> | ||
=== Remove Empty Lines === | === Remove Empty Lines === | ||
| Line 292: | Line 308: | ||
'''Using Notepad++ v7.8.7:''' - Menu: Edit -> Line Operations -> Remove Empty Lines (Including Blank Lines) | '''Using Notepad++ v7.8.7:''' - Menu: Edit -> Line Operations -> Remove Empty Lines (Including Blank Lines) | ||
=== Find Non-Whitespace Text === | === Find Non-Whitespace Text === | ||
* Find: <code>[^\s]+</code> | * Find: <code>[^\s]+</code> | ||
=== Convert Symbol-Separated Text to Line-by-Line Display === | === Convert Symbol-Separated Text to Line-by-Line Display === | ||
| Line 311: | Line 325: | ||
'''Using Sublime Text or EmEditor:''' - Find: <code>([^、]+)([、]{1})</code> - Replace with: <code>\1\n</code> | '''Using Sublime Text or EmEditor:''' - Find: <code>([^、]+)([、]{1})</code> - Replace with: <code>\1\n</code> | ||
=== Replace Multiple Spaces with Tab Characters === | === Replace Multiple Spaces with Tab Characters === | ||
| Line 318: | Line 331: | ||
'''Using Sublime Text:''' - Find: <code>([^\S\n]+)</code> or <code>([^\S\r\n]+)</code> or <code>\s\s+</code> - Replace with: <code>\t</code> | '''Using Sublime Text:''' - Find: <code>([^\S\n]+)</code> or <code>([^\S\r\n]+)</code> or <code>\s\s+</code> - Replace with: <code>\t</code> | ||
=== Remove Leading/Trailing Whitespace === | === Remove Leading/Trailing Whitespace === | ||
==== Remove Leading Whitespace ==== | ==== Remove Leading Whitespace ==== | ||
| Line 327: | Line 340: | ||
* Replace with: (empty) | * Replace with: (empty) | ||
==== Remove Trailing Whitespace ==== | ==== Remove Trailing Whitespace ==== | ||
| Line 333: | Line 346: | ||
* Replace with: (empty) | * Replace with: (empty) | ||
==== Remove Both Leading and Trailing Whitespace ==== | ==== Remove Both Leading and Trailing Whitespace ==== | ||
| Line 339: | Line 352: | ||
* Replace with: (empty) | * Replace with: (empty) | ||
== Text Editors Supporting Regular Expressions == | == Text Editors Supporting Regular Expressions == | ||
Various text editors support regular expressions including: - Sublime Text - EmEditor - Notepad++ - Visual Studio Code - Atom - Vim/Neovim | Various text editors support regular expressions including: - Sublime Text - EmEditor - Notepad++ - Visual Studio Code - Atom - Vim/Neovim | ||
== Syntax Reference == | == Syntax Reference == | ||
| Line 352: | Line 365: | ||
* Non-whitespace: <code>\S</code> - Does not include half-width spaces and full-width spaces | * Non-whitespace: <code>\S</code> - Does not include half-width spaces and full-width spaces | ||
== Troubleshooting Regular Expressions == | == 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., <code>\d</code> to <code>[0-9]+</code>) | '''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., <code>\d</code> to <code>[0-9]+</code>) | ||
== Alternative Solutions == | == Alternative Solutions == | ||
| Line 363: | Line 375: | ||
* Copy multiple rows and paste between different applications (compatibility varies) | * Copy multiple rows and paste between different applications (compatibility varies) | ||
== Further Reading == | == Further Reading == | ||
| Line 370: | Line 381: | ||
* Platform-specific regular expression documentation | * Platform-specific regular expression documentation | ||
{{Template:Data factory flow}} | {{Template: Data factory flow}} | ||
[[Category:Regular expression]] [[Category:Software]] [[Category:Programming]] [[Category:Data Science]] [[Category:Search]] [[Category:String manipulation]] | [[Category: Regular expression]] | ||
[[Category: Software]] | |||
[[Category: Programming]] | |||
[[Category: Data Science]] | |||
[[Category: Search]] | |||
[[Category: String manipulation]] | |||
[[Category: Revised with LLMs] | |||