Regular expression - Revision history

Planetoid at 03:55, 11 December 2025

2025-12-11T03:55:58Z

← Older revision		Revision as of 11:55, 11 December 2025
Line 3:		Line 3:
	{{LanguageSwitcher \| content = [[Regular expression \| English]], [[Regular expression in Mandarin\|漢字]]}}		{{LanguageSwitcher \| content = [[Regular expression \| English]], [[Regular expression in Mandarin\|漢字]]}}

	~~<blockquote>~~'''Need Help?''' You can use the provided explanatory [[#regular-expression-online-tools\|online tools]] to try debugging yourself.		{{Raise hand \| text = '''Need Help?''' You can use the provided explanatory [[#regular-expression-online-tools\|online tools]] to try debugging yourself. }}
	~~</blockquote>~~

	== Quick Reference Table ==		== Quick Reference Table ==

Planetoid at 11:07, 10 December 2025

2025-12-10T11:07:26Z

← Older revision		Revision as of 19:07, 10 December 2025
Line 121:		Line 121:
	Convert to:		Convert to:
	aaa@email.com,bbb@email.com,ccc@email.com</pre>		aaa@email.com,bbb@email.com,ccc@email.com</pre>
	~~<span id="method-1-sublime-text-emeditor"></span>~~
	==== Method 1: Sublime Text, EmEditor ====		==== Method 1: Sublime Text, EmEditor ====

Line 139:		Line 139:
	# Click “Replace All”		# Click “Replace All”

	~~<span id="method-3-microsoft-word"></span>~~
	==== Method 3: Microsoft Word ====		==== Method 3: Microsoft Word ====

Line 148:		Line 148:
	# Click “Replace All”		# Click “Replace All”

	~~<span id="method-4-sed-command-for-linux"></span>~~
	==== 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>
	~~<span id="find-ip-addresses-ipv4"></span>~~
	=== Find IP Addresses (IPv4) ===		=== Find IP Addresses (IPv4) ===

Line 159:		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>

	~~<span id="remove-black-squares-unix-line-endings-lf"></span>~~
	=== 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)

	~~<span id="add-quotes-around-elements"></span>~~
	=== Add Quotes Around Elements ===		=== Add Quotes Around Elements ===

	~~<span id="add-quotes-around-array-elements"></span>~~
	==== Add Quotes Around Array Elements ====		==== Add Quotes Around Array Elements ====

Line 184:		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)

	~~<span id="find-non-ascii-characters-chinesenon-english-text"></span>~~
	=== Find Non-ASCII Characters (Chinese/Non-English Text) ===		=== Find Non-ASCII Characters (Chinese/Non-English Text) ===

Line 289:		Line 284:
	echo "Contains non-ASCII characters";		echo "Contains non-ASCII characters";
	}</syntaxhighlight>		}</syntaxhighlight>
	~~<span id="remove-empty-lines"></span>~~
	=== Remove Empty Lines ===		=== Remove Empty Lines ===

Line 313:		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)

	~~<span id="find-non-whitespace-text"></span>~~
	=== Find Non-Whitespace Text ===		=== Find Non-Whitespace Text ===

	* Find: <code>[^\s]+</code>		* Find: <code>[^\s]+</code>

	~~<span id="convert-symbol-separated-text-to-line-by-line-display"></span>~~
	=== Convert Symbol-Separated Text to Line-by-Line Display ===		=== Convert Symbol-Separated Text to Line-by-Line Display ===

Line 332:		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>

	~~<span id="replace-multiple-spaces-with-tab-characters"></span>~~
	=== Replace Multiple Spaces with Tab Characters ===		=== Replace Multiple Spaces with Tab Characters ===

Line 339:		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>

	~~<span id="remove-leadingtrailing-whitespace"></span>~~
	=== Remove Leading/Trailing Whitespace ===		=== Remove Leading/Trailing Whitespace ===

	~~<span id="remove-leading-whitespace"></span>~~
	==== Remove Leading Whitespace ====		==== Remove Leading Whitespace ====

Line 348:		Line 340:
	* Replace with: (empty)		* Replace with: (empty)

	~~<span id="remove-trailing-whitespace"></span>~~
	==== Remove Trailing Whitespace ====		==== Remove Trailing Whitespace ====

Line 354:		Line 346:
	* Replace with: (empty)		* Replace with: (empty)

	~~<span id="remove-both-leading-and-trailing-whitespace"></span>~~
	==== Remove Both Leading and Trailing Whitespace ====		==== Remove Both Leading and Trailing Whitespace ====

Line 360:		Line 352:
	* Replace with: (empty)		* Replace with: (empty)

	~~<span id="text-editors-supporting-regular-expressions"></span>~~
	== 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

	~~<span id="syntax-reference"></span>~~
	== Syntax Reference ==		== Syntax Reference ==

Line 373:		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

	~~<span id="troubleshooting-regular-expressions"></span>~~
	== 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>)

	~~<span id="alternative-solutions"></span>~~
	== Alternative Solutions ==		== Alternative Solutions ==

Line 384:		Line 375:
	* Copy multiple rows and paste between different applications (compatibility varies)		* Copy multiple rows and paste between different applications (compatibility varies)

	~~<span id="further-reading"></span>~~
	== Further Reading ==		== Further Reading ==

Line 391:		Line 381:
	* Platform-specific regular expression documentation		* Platform-specific regular expression documentation

	{{Template:Data factory flow}}		{{Template: Data factory flow}}

	[[Category: Regular expression]]		[[Category: Regular expression]]

Planetoid: /* Find Non-ASCII Characters (Chinese/Non-English Text) */

2025-12-10T11:05:47Z

Find Non-ASCII Characters (Chinese/Non-English Text)

← Older revision		Revision as of 19:05, 10 December 2025
Line 187:		Line 187:
	=== Find Non-ASCII Characters (Chinese/Non-English Text) ===		=== Find Non-ASCII Characters (Chinese/Non-English Text) ===

	~~<span id="in-google-sheets"></span>~~
	~~==== In Google Sheets ====~~

	~~<pre>[^\x00-\x80]+</pre>~~
	~~<span id="in-libreoffice"></span>~~
	==== In LibreOffice ====		==== In LibreOffice ====

	<pre>[^\u0000-\u0080]+</pre>		<pre>[^\u0000-\u0080]+</pre>
	~~<span id="find-chinese-characters-in-google-sheets"></span>~~

	==== Find Chinese Characters in Google Sheets ====		==== Find Chinese Characters in Google Sheets ====

Line 201:		Line 198:

	<pre>=IF(REGEXMATCH(A2, "[\一-\龥]"), "Chinese", "English")</pre>		<pre>=IF(REGEXMATCH(A2, "[\一-\龥]"), "Chinese", "English")</pre>
	~~<span id="find-chinese-characters-in-mysql"></span>~~

	==== Find Non-ASCII Characters in Google Sheets ====		==== Find Non-ASCII Characters in Google Sheets ====
Line 243:		Line 239:
	FROM `table_name`		FROM `table_name`
	WHERE `column_name` <> CONVERT(`column_name` USING ASCII)</syntaxhighlight>		WHERE `column_name` <> CONVERT(`column_name` USING ASCII)</syntaxhighlight>
	~~<span id="find-chinese-characters-in-php"></span>~~
	==== Find Chinese Characters in PHP ====		==== Find Chinese Characters in PHP ====

Line 274:		Line 270:
	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>
	~~<span id="find-ascii-characters-in-php"></span>~~
	=== Find ASCII Characters in PHP ===		=== Find ASCII Characters in PHP ===

Planetoid: /* Find Chinese Characters in Google Sheets */

2025-12-10T11:04:51Z

Find Chinese Characters in Google Sheets

← Older revision		Revision as of 19:04, 10 December 2025
Line 203:		Line 203:
	<span id="find-chinese-characters-in-mysql"></span>		<span id="find-chinese-characters-in-mysql"></span>

			==== Find Non-ASCII Characters in Google Sheets ====
	Extract non-ASCII characters (such as Chinese, Japanese, emoji, etc.) from cell {{kbd \| key=A2}}		Extract non-ASCII characters (such as Chinese, Japanese, emoji, etc.) from cell {{kbd \| key=A2}}
	<pre>		<pre>

Planetoid at 11:03, 10 December 2025

2025-12-10T11:03:41Z

← Older revision		Revision as of 19:03, 10 December 2025
Line 396:		Line 396:
	{{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]

Planetoid: /* Find Chinese Characters in Google Sheets */

2025-12-10T11:02:40Z

Find Chinese Characters in Google Sheets

← Older revision		Revision as of 19:02, 10 December 2025
Line 198:		Line 198:
	==== 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>
	<span id="find-chinese-characters-in-mysql"></span>		<span id="find-chinese-characters-in-mysql"></span>

			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 ====

Planetoid at 11:12, 25 September 2025

2025-09-25T11:12:25Z

← Older revision		Revision as of 19:12, 25 September 2025
Line 5:		Line 5:
	<blockquote>'''Need Help?''' You can use the provided explanatory [[#regular-expression-online-tools\|online tools]] to try debugging yourself.		<blockquote>'''Need Help?''' You can use the provided explanatory [[#regular-expression-online-tools\|online tools]] to try debugging yourself.
	</blockquote>		</blockquote>
	~~<span id="quick-reference-table"></span>~~
	== Quick Reference Table ==		== Quick Reference Table ==

Line 93:		Line 93:
	\|}		\|}

	~~<span id="regular-expression-online-tools"></span>~~
	== Regular Expression Online Tools ==		== Regular Expression Online Tools ==

Line 106:		Line 106:


	~~<span id="common-use-cases"></span>~~

	== Common Use Cases ==		== Common Use Cases ==

	~~<span id="replace-newlines-with-commas"></span>~~
	=== Replace Newlines with Commas ===		=== Replace Newlines with Commas ===

Line 131:		Line 130:
	# Click “Replace all”		# Click “Replace all”

	~~<span id="method-2-notepad"></span>~~
	==== Method 2: Notepad++ ====		==== Method 2: Notepad++ ====

Planetoid: /* Find Chinese Characters in MySQL */

2025-09-25T11:11:16Z

Find Chinese Characters in MySQL

← Older revision		Revision as of 19:11, 25 September 2025
Line 207:		Line 207:
	Find rows where <code>column_name</code> contains Chinese characters:		Find rows where <code>column_name</code> contains Chinese characters:

	<~~syntaxhighlight~~ lang="sql">SELECT `column_name`		<pre lang="sql">SELECT `column_name`
	FROM `table_name`		FROM `table_name`
	WHERE HEX(`column_name`) REGEXP '^(..)*(E[4-9])';</~~syntaxhighlight~~>		WHERE HEX(`column_name`) REGEXP '^(..)*(E[4-9])';</pre>
	<~~span id~~="~~find~~-~~non~~-~~ascii~~-characters-in-~~mysql~~"></~~span~~>
			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`
			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 ====

Planetoid: /* Regular Expression Online Tools */

2025-07-21T13:51:54Z

Regular Expression Online Tools

← Older revision		Revision as of 21:51, 21 July 2025
Line 96:		Line 96:
	== Regular Expression Online Tools ==		== 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 - Visual explanation using diagrams * ~~'''~~Rubular~~'''~~ - A Ruby regular expression editor and tester * ~~'''~~PHP Live Regex~~'''~~ * ~~'''~~Regex Tester and Debugger Online~~'''~~ - ~~Javascript~~, PCRE, PHP		Websites for testing regular expression syntax:
			* {{Gd}} [http://regex101.com/ RegEx101] - “Online regex tester and debugger: PHP, PCRE, Python, Golang and JavaScript” - Provides syntax explanations
			* {{Gd}} [http://gskinner.com/RegExr/ RegExr] - Learn, Build, & Test RegEx - Provides syntax explanations
			* [https://regexper.com/ Regexper] - Visual explanation of syntax using diagrams
			* [https://jex.im/regulex/ Regulex：JavaScript Regular Expression Visualizer] - JavaScript Regular Expression Visualizer - Visual explanation using diagrams
			* [http://www.rubular.com/ Rubular] - A Ruby regular expression editor and tester
			* [http://www.phpliveregex.com/ PHP Live Regex]
			* [http://www.regextester.com/ Regex Tester and Debugger Online] - JavaScript, PCRE, PHP

	~~Examples: * '''Regular Expression Library''' - Pattern examples provided by users~~

	<span id="common-use-cases"></span>		<span id="common-use-cases"></span>

	== Common Use Cases ==		== Common Use Cases ==

Planetoid at 13:48, 21 July 2025

2025-07-21T13:48:58Z

← Older revision		Revision as of 21:48, 21 July 2025
Line 1:		Line 1:
	= Regular ~~Expression Guide =~~		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.

	~~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.~~		{{LanguageSwitcher \| content = [[Regular expression \| English]], [[Regular expression in Mandarin\|漢字]]}}

	Regular ~~expressions are also known as regex, regexp, or pattern matching expressions.~~

	<blockquote>'''Need Help?''' You can use the provided explanatory [[#regular-expression-online-tools\|online tools]] to try debugging yourself.		<blockquote>'''Need Help?''' You can use the provided explanatory [[#regular-expression-online-tools\|online tools]] to try debugging yourself.