Re^3: Line separators when passing multi-line fields to a database ("\n")

'\n' eq '\\n'; they are both two characters, a backslash (\) followed by the letter en (n). When you use placeholders, passing '\n' in as a value acts the same as putting a literal '\\n' into the SQL (at least for SQLs where '\\' is how you get a single backslash character).

"\n" is a newline. When you use placeholders, passing "\n" in as a value acts the same as either putting '\n' (on some databases) or ' ' (a literal newline between single quotes) into the SQL.

It sounds like all you need is s/\r\n/\n/g (unless you need to run your script on old Macs, in which case you want something ugly like:

my( $cr, $lf )= ( "\r", "\n" );
( $cr, $lf )= ( "\n", "\r" )
    if  "\r" eq "\x0a"; # On an old Mac

    ... s/$cr$lf/$lf/g;
[download]

or you can just ignore the currently even rarer possibility of non-ASCII Perl and do s/\x0d\x0a/\x0a/g, but I refuse to go back to the "hard code character encodings" school of programming even if the current thinking is that Unicode will be the one ring to rule them all forever... eventually).

- tye

Comment on Re^3: Line separators when passing multi-line fields to a database ("\n") Select or Download Code

Replies are listed 'Best First'.
Re^4: Line separators when passing multi-line fields to a database ("\n") by davies (Monsignor) on Mar 23, 2011 at 14:26 UTC
OK, I have what I need. I had several problems that I will confess for the record. While I don't think it compounded the problem, my misunderstanding of Perl's \n that tye corrected would, I'm sure, have slowed me down eventually. Second, I hadn't seen Perl's \r before, didn't recognise it at first and, for no good reason, started thinking that \n was 0D and \r was 0A. Third, I was flailing at demons and trying to sort out the \n and \r combinations in text, instead of working with hex or decimal character codes. This led to a fourth problem, namely that I was trying to resolve the difference between \n as a two character string and 0A which gets db->quoted to \n. Fifth, I was unsure about the workings of \n in regexes and tried to do it a way I understood instead of a way that worked. My final solution is breathtakingly simple. `s/\r\n/\n/g` does everything I need. Even with the help I got here, it took the thick end of a day to unconfuse myself. I dread to think how long it would have taken on my own. Plusses and kisses to all who helped! :-) Regards, John Davies Update - much, much later. I continued to flail at demons even after posting the above. Strange things were happening that I could not explain and my database was not being updated. The regex I gave above, `s/\r\n/\n/g`, works provided there is only one line break, the case I was testing when I wrote my original query. What I really needed was `s/\r\n/\n/gms` to cover the case of three or more lines.	[reply] [d/l] [select]

Replies are listed 'Best First'.

Re^4: Line separators when passing multi-line fields to a database ("\n")
by davies (Monsignor) on Mar 23, 2011 at 14:26 UTC

OK, I have what I need. I had several problems that I will confess for the record. While I don't think it compounded the problem, my misunderstanding of Perl's \n that tye corrected would, I'm sure, have slowed me down eventually. Second, I hadn't seen Perl's \r before, didn't recognise it at first and, for no good reason, started thinking that \n was 0D and \r was 0A. Third, I was flailing at demons and trying to sort out the \n and \r combinations in text, instead of working with hex or decimal character codes. This led to a fourth problem, namely that I was trying to resolve the difference between \n as a two character string and 0A which gets db->quoted to \n. Fifth, I was unsure about the workings of \n in regexes and tried to do it a way I understood instead of a way that worked.

My final solution is breathtakingly simple. s/\r\n/\n/g does everything I need. Even with the help I got here, it took the thick end of a day to unconfuse myself. I dread to think how long it would have taken on my own.

Plusses and kisses to all who helped! :-)

Regards,

John Davies

Update - much, much later. I continued to flail at demons even after posting the above. Strange things were happening that I could not explain and my database was not being updated. The regex I gave above, s/\r\n/\n/g, works provided there is only one line break, the case I was testing when I wrote my original query. What I really needed was s/\r\n/\n/gms to cover the case of three or more lines.

[reply]
[d/l]
[select]