Return symbol: Difference between revisions

From LemonWiki共筆
Jump to navigation Jump to search
Tags: Mobile edit Mobile web edit
Line 111: Line 111:


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


如何取代換行符號
<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>
 
 
=== 文字編輯軟體如何取代換行符號 ===
* 支援 [[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:
=== Excel 如何取代換行符號 ===
** {{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>=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=<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(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>}}
** {{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}}
=== 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 ==

Revision as of 18:06, 8 August 2025

如何看到換行符號、如何從文章中取代換行符號 (英文：Return symbol, line terminators, line endings, newline, end of line (EOL), line feed (LF) or line break)。

  • On Win Os windows.png : CRLF = \r\n
  • On Unix, Linux Os linux.png & recent versions of macOS icon_os_mac.png : LF = \n
  • On classic versions of macOS icon_os_mac.png [1][2] e.g. Mac OS 9: CR = \r

如何使用編輯軟體，看到換行符號

查看每一行使用的換行符號

  • Notepad++ On Win Os windows.png 選單 → 檢視 → 特殊字元 → 顯示行尾字元 (EOL)
  • RawLineEdit for Sublime text
    • 換行符號是 CRLF = \r\n 每行最後顯示的是 <0x0d>¬
    • 換行符號是 LF = \n 每行最後顯示的是 ¬ 象形符號 (glyph)
    • 換行符號是 CR = \r 每行最後顯示的是 <0x0d>
  • BASH cat command e.g. cat -e <filename>
    • 換行符號是 CRLF = \r\n 每行最後顯示的是 ^M$
    • 換行符號是 LF = \n 每行最後顯示的是 $
    • 換行符號是 CR = \r 每行最後顯示的是 ^M

查看檔案使用的換行符號

檔案內容 File command 偵測結果
換行符號是 CRLF = \r\n UTF-8 Unicode text, with CRLF line terminators
換行符號是 LF = \n UTF-8 Unicode text Icon_exclaim.gif
換行符號是 CR = \r UTF-8 Unicode text, with CR line terminators
特例: 換行符號夾雜 \r\n 和 \r UTF-8 Unicode text, with CRLF, CR line terminators
特例: 換行符號夾雜 \r\n 和 \n UTF-8 Unicode text, with CRLF, LF line terminators
特例: 換行符號夾雜 \n 和 \r UTF-8 Unicode text, with CR, LF line terminators
特例: 無內容的空檔案 empty

不同換行符號 在不同作業系統的編輯器看到的文件狀況

檔案內容 Windows 10 內建「記事本」 Windows 10 免費編輯器 Notepad++ v. 7.5.9 Mac 內建「文字編輯.app」 Mac 編輯器 Sublime Text v. 3.2
換行符號是 CRLF = \r\n ok ok ok ok
換行符號是 LF = \n Icon_exclaim.gif 預期不同行的內容擠在一起 ok ok ok
換行符號是 CR = \r Icon_exclaim.gif 預期不同行的內容擠在一起 ok ok ok

計算換行符號的數量

## 計算 \n (不包含 \r\n) 換行符號的數量
% perl -ne '$count++ if /[^\r]\n/; END{print "純 LF 數量: $count\n"}' YOUR_FILE.txt

## 計算 \r\n 換行符號的數量
% perl -ne '$count++ if /\r\n/; END{print "CRLF 數量: $count\n"}' YOUR_FILE.txt

如何尋找與取代換行符號

MySQL 如何尋找換行符號

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), ']');

MySQL 如何取代換行符號

[4][5] 

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','')
;


文字編輯軟體如何取代換行符號


Excel 如何取代換行符號

  • =CLEAN(A1) Use the CLEAN function to "remove the first 32 nonprinting characters in the 7-bit ASCII code (values 0 through 31) from the text"[6]. Meanwhile, the return symbol will be removed from the text.
  • =TRIM(SUBSTITUTE(SUBSTITUTE(A1, CHAR(13),""), CHAR(10),", ") to replace the return symbol in the cell located at A1 [7][8].
    • CHAR(10) means "Line feed" which equal to \n
    • CHAR(13) means "Carriage return" which equal to \r

Python 如何取代換行符號

Related articles

References

  1. ↑ Classic Mac OS - Wikipedia
  2. ↑ Newline - Wikipedia
  3. ↑ file(1) Mac OS X Manual Page
  4. ↑ sql - REPLACE new line character in MYSql not working - Stack Overflow
  5. ↑ MySQL: trim *both* whitespace and newline characters - Stack Overflow
  6. ↑ CLEAN function
  7. ↑ 3 ways to remove carriage returns in Excel: formulas, VBA macro, find&replace dialog [Last visited: 2018-05-24]
  8. ↑ ASCII - Wikipedia
Retrieved from "https://wiki.planetoid.info/index.php?title=Return_symbol&oldid=25912"