views:

2528

answers:

11

When I run a particular SQL script in Unix environments, I'm am seeing a '^M' character at the end of each line of the SQL script as it is echoed to the command-line. I don't know on which OS the SQL script was originally created.

What is causing this and how do I fix it?

+3  A: 

Try using dos2unix to strip off the ^M.

Andy Whitfield
+9  A: 

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.

Thomas Owens
+3  A: 

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

dos2unix [options] [-c convmode] [-o file ...] [-n infile outfile ...]

jW
+1  A: 

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.

perl -pie 's/\r//g' filename.txt
Bill the Lizard
Sure, you CAN use perl, but would you suggest perl over dos2unix?
Thomas Owens
I'm just providing an alternative, since four people already said to use dos2unix.
Bill the Lizard
I found this alternative useful, for a different case, so thanks.
Ofri Raviv
Yes, I found this useful because I am on a backward workstation working in an office with a prehistoric IT department.Except I used a variation: perl -pi -e "s/\x0D/\n/g" file.csv
Rimian
+1  A: 

In vi, do a :%s/^M//g

To get the ^M hold the control key, press V then M (Both while holding the control key) and the ^M will appear. This will find all occurances and replace them with nothing.

dogbane
+2  A: 

The cause is the difference between how a Windows-based based OS and a Unix based OS store the end-of-line markers.

Windows based operating systems, thanks to their DOS heritage, store an end-of-line as a pair of characters - 0x0A0D. Unix based operating systems just use 0x0A. The ^M you're seeing is a visual representation of 0x0D.

dos2unix will help with this. You probably also need to adjust the source of the scripts to be 'unix friendly'.

ColinYounger
A: 

The easiest way is to use VI. I know that sounds terrible but its simple and already installed on most UNIX environments. The ^M is a new line from Windows/DOS environment.

from the command prompt: $ vi filename

Then press ":" to get to command mode.

Search and Replace all Globally is :%s/^M//g "Press and hold control then press V then M" which will replace ^M with nothing.

Then to write and quit enter ":wq" Done!

Bernie Perez
A: 

Another vi command that'll do: :%s/.$// 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.

Scottie T
A: 
C:\tmp\text>dos2unix hello.txt helloUNIX.txt

Sed is even more widely available andcan do this kind of thing also if dos2unix is not installed

C:\tmp\text>sed s/\r// hello.txt > helloUNIX.txt

You could also try tr:

cat hello.txt | tr -d \r > helloUNIX2.txt

Here are the results:

C:\tmp\text>dumphex hello.txt  
00000000h: 48 61 68 61 0D 0A 68 61 68 61 0D 0A 68 61 68 61 Haha..haha..haha  
00000010h: 0D 0A 0D 0A 68 61 68 61 0D 0A                   ....haha..  

C:\tmp\text>dumphex helloUNIX.txt  
00000000h: 48 61 68 61 0A 68 61 68 61 0A 68 61 68 61 0A 0A Haha.haha.haha..  
00000010h: 68 61 68 61 0A                                  haha.  

C:\tmp\text>dumphex helloUNIX2.txt  
00000000h: 48 61 68 61 0A 68 61 68 61 0A 68 61 68 61 0A 0A Haha.haha.haha..  
00000010h: 68 61 68 61 0A                                  haha.
A: 

od -a $file is useful to explore those types of question on Linux (similar to dumphex in the above).

Allan Wind
+1  A: 

fix line endings in vi:

:set fileformat=unix

:w

Tim Abell