Q:

Difference in string length (database - .txt file)

I'm working on a project where I load records from a database and run some validation checks on them such as pattern searching which returns me the index of a certain pattern. The records themselves can be very big, with one field up to 2 million characters. For this reason I have copied some test data into plain text files and saved them locally.
The problem I have is that when I run the tests, the strings I read from the local text file differ in length from the string inside the MySQL database. Indexes returned from the SQL database are lower (the complete string is shorter) than the indexes returned from the plain text file I stored locally.
example of database field (type = mediumtext)
11:00 i have started entering into this database field
12:00 continueing
13:00 some more
14:00 end of database work

When I check the database field for its length (using select length(field)) it tells me its length is 115
When i paste the exact contents of the database field into Notepad++, it tells me there are 118 characters.
I know for a fact that its not a set difference because the difference in indexes returned by my code is increasing as i go further into long strings.
My guess is that MySQL stores linebreaks/spaces/tabs differently than a .txt file.
But this is just a wild guess.
Any help is appreciated.
Thanks in advance

A:

Since you already found out that the database is using \n as newline and the textfile is using \r\n as newline (Thanks to Tom). I found a solution for you which you maybe can use.
You could replace the \n with \r\n so it matches your text file.
You can use this: REPLACE(column, '\n', '\r\n') in your select query.

