LemonWiki共筆

Return symbol: Difference between revisions

← Older edit
Tags: Mobile edit Mobile web edit
Tag: wikieditor
 
(13 intermediate revisions by the same user not shown)
Line 111: Line 111:


== 如何尋找與取代換行符號 ==
== 如何尋找與取代換行符號 ==
如何尋找換行符號
* MySQL:
** {{kbd | key=<nowiki>SELECT * FROM `my_table` WHERE `my_column` REGEXP CHAR(10)</nowiki>}}


如何取代換行符號
=== 文字編輯軟體如何取代換行符號 ===
* 支援 [[Regular expression]] 的文字編輯軟體，例: [[Regular expression#將Email清單，轉成Email軟體可以使用的寄信名單 (取代換行符號為逗號) | 將Email清單，轉成Email軟體可以使用的寄信名單 (取代換行符號為逗號)]]
* 支援 [[Regular expression]] 的文字編輯軟體，例: [[Regular expression#將Email清單，轉成Email軟體可以使用的寄信名單 (取代換行符號為逗號) | 將Email清單，轉成Email軟體可以使用的寄信名單 (取代換行符號為逗號)]]
* MySQL:
** {{kbd | key=<nowiki>SELECT REPLACE(REPLACE(REPLACE(`column`,'\r\n',''),'\n',''),'\r','') </nowiki>}} <ref> [https://stackoverflow.com/questions/13273343/replace-new-line-character-in-mysql-not-working sql - REPLACE new line character in MYSql not working - Stack Overflow] </ref> or
** {{kbd | key=<nowiki>SELECT TRIM(REPLACE(`column`, '\n','' ))</nowiki>}} <ref>[https://stackoverflow.com/questions/28368785/mysql-trim-both-whitespace-and-newline-characters MySQL: trim *both* whitespace and newline characters - Stack Overflow]</ref>




* Excel:
=== MySQL 如何尋找換行符號 ===
** {{kbd | key=<nowiki>=CLEAN(A1)</nowiki>}} Use the CLEAN function to "remove the first 32 nonprinting characters in the 7-bit ASCII code (values 0 through 31) from the text"<ref>[https://support.microsoft.com/en-us/office/clean-function-26f3d7c5-475f-4a9c-90e5-4b8ba987ba41 CLEAN function]</ref>. Meanwhile, the return symbol will be removed from the text.
** {{kbd | key=<nowiki>=TRIM(SUBSTITUTE(SUBSTITUTE(A1, CHAR(13),""), CHAR(10),", ")</nowiki>}} to replace the return symbol in the cell located at {{kbd | key=A1}} <ref>[https://www.ablebits.com/office-addins-blog/2013/12/03/remove-carriage-returns-excel/ 3 ways to remove carriage returns in Excel: formulas, VBA macro, find&replace dialog] {{access | date = 2018-05-24}}</ref><ref>[https://en.wikipedia.org/wiki/ASCII ASCII - Wikipedia]</ref>.
*** {{kbd | key=CHAR(10)}} means "Line feed" which equal to {{kbd | key=<nowiki>\n</nowiki>}}
*** {{kbd | key=CHAR(13)}} means "Carriage return" which equal to {{kbd | key=<nowiki>\r</nowiki>}}


* Python way: [https://stackoverflow.com/questions/275018/how-can-i-remove-a-trailing-newline-in-python How can I remove a trailing newline in Python? - Stack Overflow] {{access | date = 2019-06-14}}
<pre>
SELECT content,
      CASE
          WHEN content LIKE CONCAT('%', CHAR(13), CHAR(10), '%') THEN 'CRLF (\\r\\n)'
          WHEN content LIKE CONCAT('%', CHAR(10), '%') THEN 'LF (\\n)'
          WHEN content LIKE CONCAT('%', CHAR(13), '%') THEN 'CR (\\r)'
          ELSE 'No newlines'
      END as newline_type
FROM my_table
WHERE content REGEXP CONCAT('[', CHAR(10), CHAR(13), ']');
</pre>
 
=== MySQL 如何取代換行符號 ===
 
<ref> [https://stackoverflow.com/questions/13273343/replace-new-line-character-in-mysql-not-working sql - REPLACE new line character in MYSql not working - Stack Overflow] </ref><ref>[https://stackoverflow.com/questions/28368785/mysql-trim-both-whitespace-and-newline-characters MySQL: trim *both* whitespace and newline characters - Stack Overflow]</ref>
 
<pre>
UPDATE my_table
SET content = REPLACE(
    REPLACE(
        REPLACE(content, CONCAT(CHAR(13), CHAR(10)), ''),
        CHAR(10), ''
    ),
    CHAR(13), ''
)
WHERE content REGEXP CONCAT('[', CHAR(10), CHAR(13), ']');
 
-- or
 
UPDATE my_table
SET `content` = REPLACE(REPLACE(REPLACE(`my_table`,'\r\n',''),'\n',''),'\r','')
;
 
</pre>
 
=== Excel 或 Google Sheet 如何尋找換行符號 ===
判斷是哪種換行符號：
<pre>
=IF(ISNUMBER(SEARCH(CHAR(13)&CHAR(10), A1)), "CRLF (\r\n)",
  IF(ISNUMBER(SEARCH(CHAR(13), A1)), "CR (\r)",
    IF(ISNUMBER(SEARCH(CHAR(10), A1)), "LF (\n)",
      "無換行符號")))
</pre>
 
=== Excel 或 Google Sheet 如何取代換行符號 ===
For Microsoft Excel or Google Sheet
* {{kbd | key=<nowiki>=CLEAN(A1)</nowiki>}} Use the CLEAN function to "remove the first 32 nonprinting characters in the 7-bit ASCII code (values 0 through 31) from the text"<ref>[https://support.microsoft.com/en-us/office/clean-function-26f3d7c5-475f-4a9c-90e5-4b8ba987ba41 CLEAN function]</ref>. Meanwhile, the return symbol will be removed from the text.
* {{kbd | key=<nowiki>=TRIM(SUBSTITUTE(SUBSTITUTE(A1, CHAR(13),""), CHAR(10),", ")</nowiki>}} replace the return symbols with spaces  in the cell located at {{kbd | key=A1}} <ref>[https://www.ablebits.com/office-addins-blog/2013/12/03/remove-carriage-returns-excel/ 3 ways to remove carriage returns in Excel: formulas, VBA macro, find&replace dialog] {{access | date = 2018-05-24}}</ref><ref>[https://en.wikipedia.org/wiki/ASCII ASCII - Wikipedia]</ref>.
 
函數說明
* {{kbd | key=CONCAT(CHAR(13), CHAR(10))}} means "CRLF"({{kbd | key=<nowiki>\r\n</nowiki>}}) Windows 標準換行符號
* {{kbd | key=CHAR(13)}} means "Carriage return"(CR, {{kbd | key=<nowiki>\r</nowiki>}}) 舊版 Mac 換行符號
* {{kbd | key=CHAR(10)}} means "Line feed" (LF, {{kbd | key=<nowiki>\n</nowiki>}}) Unix/Linux 換行符號
 
For Google Sheet
* {{kbd | key=<nowiki>=REGEXREPLACE(A1, "[\r\n]+", " ")</nowiki>}} replaces return symbols with spaces in cell {{kbd | key=A1}} in Google Sheets
 
函數說明
* {{kbd | key=<nowiki>[\r\n]</nowiki>}} 符合任意類型的換行符號
* {{kbd | key=<nowiki>[\r\n]+</nowiki>}} 符合任意類型的換行符號，出現一次或連續出現
 
=== Python 如何取代換行符號 ===
* [https://stackoverflow.com/questions/275018/how-can-i-remove-a-trailing-newline-in-python How can I remove a trailing newline in Python? - Stack Overflow] {{access | date = 2019-06-14}}


== Related articles ==
== Related articles ==
Line 144: Line 196:
<references />
<references />


[[Category:Programming]] [[Category:Data Science]] [[Category:String manipulation]]
[[Category: Programming]]  
[[Category: Data Science]]  
[[Category: String manipulation]]
[[Category: Revised with LLMs]]
Retrieved from "https://wiki.planetoid.info/index.php/Return_symbol"