<p>When I run a particular SQL script in Unix environments, I see a '^M' character at the end of each line of the SQL script as it is echoed to the command line.<br />
I don't know on which OS the SQL script was initially created.</p>
<p>What is causing this and how do I fix it?</p>

[{'answer_id': 64754, 'author': 'Andy Whitfield', 'author_id': 4805, 'author_profile': 'https://Stackoverflow.com/users/4805', 'pm_score': 4, 'selected': False, 'text': '<p>Try using dos2unix to strip off the ^M.</p>\n'}, {'answer_id': 64761, 'author': 'Thomas Owens', 'author_id': 572, 'author_profile': 'https://Stackoverflow.com/users/572', 'pm_score': 7, 'selected': True, 'text': "<p>It's caused by the DOS/Windows line-ending characters. Like Andy Whitfield said, the Unix command dos2unix will help fix the problem. If you want more information, you can read the man pages for that command.</p>\n"}, {'answer_id': 64772, 'author': 'jW.', 'author_id': 8880, 'author_profile': 'https://Stackoverflow.com/users/8880', 'pm_score': 3, 'selected': False, 'text': '<p>The ^M is typically caused by the Windows operator newlines, and translated onto Unix looks like a ^M. The command dos2unix should remove them nicely</p>\n\n<p>dos2unix [options] [-c convmode] [-o file ...] [-n infile outfile ...] </p>\n'}, {'answer_id': 64788, 'author': 'Bill the Lizard', 'author_id': 1288, 'author_profile': 'https://Stackoverflow.com/users/1288', 'pm_score': 3, 'selected': False, 'text': "<p>The SQL script was originally created on a Windows OS.  The '^M' characters are a result of Windows and Unix having different ideas about what to use for an end-of-line character.  You can use perl at the command line to fix this.</p>\n\n<pre><code>perl -pie 's/\\r//g' filename.txt\n</code></pre>\n"}, {'answer_id': 64792, 'author': 'dogbane', 'author_id': 7412, 'author_profile': 'https://Stackoverflow.com/users/7412', 'pm_score': 3, 'selected': False, 'text': '<p>In vi, do a <code>:%s/^M//g</code></p>\n\n<p>To get the <code>^M</code> hold the <kbd>CTRL</kbd> key, press <kbd>V</kbd> then <kbd>M</kbd> (Both while holding the control key) and the <code>^M</code> will appear. This will find all occurrences and replace them with nothing.</p>\n'}, {'answer_id': 64798, 'author': 'ColinYounger', 'author_id': 1223, 'author_profile': 'https://Stackoverflow.com/users/1223', 'pm_score': 5, 'selected': False, 'text': '<p>The cause is the difference between how a Windows-based based OS and a Unix based OS store the end-of-line markers.</p>\n\n<p>Windows based operating systems, thanks to their DOS heritage, store an end-of-line as a pair of characters - <code>0x0D0A</code> (carriage return + line feed). Unix-based operating systems just use <code>0x0A</code> (a <a href="https://en.wikipedia.org/wiki/Line_feed" rel="noreferrer">line feed</a>). The <code>^M</code> you\'re seeing is a visual representation of <code>0x0D</code> (a <a href="https://en.wikipedia.org/wiki/Carriage_return#Computers" rel="noreferrer">carriage return</a>).</p>\n\n<p><a href="http://www.linuxcommand.org/man_pages/dos2unix1.html" rel="noreferrer">dos2unix</a> will help with this. You probably also need to adjust the source of the scripts to be \'Unix-friendly\'.</p>\n'}, {'answer_id': 64959, 'author': 'Bernie Perez', 'author_id': 1992, 'author_profile': 'https://Stackoverflow.com/users/1992', 'pm_score': 5, 'selected': False, 'text': '<p>The easiest way is to use <code>vi</code>. <strike>I know that sounds terrible but</strike> its simple and already installed on most UNIX environments. The ^M is a new line from Windows/DOS environment.</p>\n\n<p>from the command prompt: <code>$ vi filename</code></p>\n\n<p>Then press "<code>:</code>" to get to command mode.</p>\n\n<p>Search and Replace all Globally is <code>:%s/^M//g</code> "<strong>Press and hold control then press V then\nM</strong>" which will replace ^M with nothing.</p>\n\n<p>Then to write and quit enter "<code>:wq</code>" Done!</p>\n'}, {'answer_id': 65404, 'author': 'Scottie T', 'author_id': 6688, 'author_profile': 'https://Stackoverflow.com/users/6688', 'pm_score': -1, 'selected': False, 'text': "<p>Another vi command that'll do: <code>:%s/.$//</code>  This removes the last character of each line in the file.  The drawback to this search and replace command is that it doesn't care what the last character is, so be careful not to call it twice.</p>\n"}, {'answer_id': 65449, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 3, 'selected': False, 'text': '<pre><code>C:\\tmp\\text&gt;dos2unix hello.txt helloUNIX.txt\n</code></pre>\n\n<p>Sed is even more widely available and can do this kind of thing also if dos2unix is not installed  </p>\n\n<pre><code>C:\\tmp\\text&gt;sed s/\\r// hello.txt &gt; helloUNIX.txt  \n</code></pre>\n\n<p>You could also try tr:  </p>\n\n<pre><code>cat hello.txt | tr -d \\r &gt; helloUNIX2.txt  \n</code></pre>\n\n<p>Here are the results:</p>\n\n<pre><code>C:\\tmp\\text&gt;dumphex hello.txt  \n00000000h: 48 61 68 61 0D 0A 68 61 68 61 0D 0A 68 61 68 61 Haha..haha..haha  \n00000010h: 0D 0A 0D 0A 68 61 68 61 0D 0A                   ....haha..  \n\nC:\\tmp\\text&gt;dumphex helloUNIX.txt  \n00000000h: 48 61 68 61 0A 68 61 68 61 0A 68 61 68 61 0A 0A Haha.haha.haha..  \n00000010h: 68 61 68 61 0A                                  haha.  \n\nC:\\tmp\\text&gt;dumphex helloUNIX2.txt  \n00000000h: 48 61 68 61 0A 68 61 68 61 0A 68 61 68 61 0A 0A Haha.haha.haha..  \n00000010h: 68 61 68 61 0A                                  haha.  \n</code></pre>\n'}, {'answer_id': 66264, 'author': 'Allan Wind', 'author_id': 9706, 'author_profile': 'https://Stackoverflow.com/users/9706', 'pm_score': 0, 'selected': False, 'text': '<p><code>od -a $file</code> is useful to explore those types of question on Linux (similar to dumphex in the above).</p>\n'}, {'answer_id': 73859, 'author': 'Tim Abell', 'author_id': 10245, 'author_profile': 'https://Stackoverflow.com/users/10245', 'pm_score': 6, 'selected': False, 'text': '<p>Fix line endings in <code>vi</code> by running the following:</p>\n\n<p><code>:set fileformat=unix</code></p>\n\n<p><code>:w</code></p>\n'}, {'answer_id': 15263125, 'author': 'Ariel Monaco', 'author_id': 445845, 'author_profile': 'https://Stackoverflow.com/users/445845', 'pm_score': 0, 'selected': False, 'text': "<p>In Perl, if you don't want to set the $/ variable and use chomp() you can also do:</p>\n\n<pre><code>$var =~ /\\r\\n//g;\n</code></pre>\n\n<p>My two cents</p>\n"}, {'answer_id': 16062926, 'author': 'leela', 'author_id': 451502, 'author_profile': 'https://Stackoverflow.com/users/451502', 'pm_score': 2, 'selected': False, 'text': '<p>To replace ^M characters in vi editor use below</p>\n\n<p>open the text file say t1.txt</p>\n\n<pre><code>vi t1.txt\n</code></pre>\n\n<p>Enter command mode by pressing <code>shift + :</code></p>\n\n<p>then press keys as mentioned <code>%s/^M/\\r/g</code> </p>\n\n<pre><code>in above ^M is not (shift + 6)M instead it is (ctrl + V)(ctrl + M)\n</code></pre>\n'}, {'answer_id': 17716712, 'author': 'g4th', 'author_id': 905598, 'author_profile': 'https://Stackoverflow.com/users/905598', 'pm_score': 3, 'selected': False, 'text': "<p>An alternative to <code>dos2unix</code> command would be using standard utilities like <code>sed</code>.</p>\n\n<p>For example,\ndos to unix:</p>\n\n<pre><code>sed 's/\\r$//' dos.txt &gt; unix.txt\n</code></pre>\n\n<p>unix to dos:</p>\n\n<pre><code>sed 's/$/\\r/' unix.txt &gt; dos.txt\n</code></pre>\n"}, {'answer_id': 21632415, 'author': 'kenorb', 'author_id': 55075, 'author_profile': 'https://Stackoverflow.com/users/55075', 'pm_score': 1, 'selected': False, 'text': "<p>You can remove ^M from the files directly via sed command, e.g.:</p>\n\n<pre><code>sed -i'.bak' s/\\r//g *.*\n</code></pre>\n\n<p>If you're happy with the changes, remove the .bak files:</p>\n\n<pre><code>rm -v *.bak\n</code></pre>\n"}, {'answer_id': 24024068, 'author': 'Mason Wright', 'author_id': 998179, 'author_profile': 'https://Stackoverflow.com/users/998179', 'pm_score': 1, 'selected': False, 'text': '<p>Convert DOS/Windows (\\r\\n) line endings to Unix (\\n) line endings, with tr:</p>\n\n<pre><code>tr \'\\r\\n\' \'\\n\' &lt; dosFile.txt &gt; unixFile.txt\n</code></pre>\n\n<p><a href="https://stackoverflow.com/a/1252010">Post about replacing newlines from the Unix command line</a></p>\n'}, {'answer_id': 70804987, 'author': 'Nagev', 'author_id': 5362795, 'author_profile': 'https://Stackoverflow.com/users/5362795', 'pm_score': 0, 'selected': False, 'text': '<p>As <a href="https://stackoverflow.com/a/64798/5362795">already explained</a>, Windows programs like to terminate lines with <em>CRLF</em>, i.e. <code>\\r\\n</code> instead of the Unix/Linux standard <code>\\n</code>. Since I don\'t need all the features of <code>dos2unix</code> I replaced it by adding the following to my <code>~/.bashrc</code> which removes the <code>\\r</code>:</p>\n<pre class="lang-bash prettyprint-override"><code>function win2unix() {\n    tmp=$(mktemp) &amp;&amp; tr -d \'\\r\' &lt; $1 &gt; $tmp &amp;&amp; mv $tmp $1\n}\n</code></pre>\n<p>Now when I want to get rid of those <code>^M</code> characters created e.g. when I export a CSV file from Excel or Calc, I can just do something like:</p>\n<pre><code>win2unix filename.csv\n</code></pre>\n<p>You could also use <code>sed</code> or something else, of course. By the way, I use <code>cat -e $filename</code> to visualize the <code>^M</code> endings.</p>\n'}]
['https://Stackoverflow.com/questions/64749', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/7648/']