Detect \n character saved in SQLite table?

Question

I designed a table with a column whose data contains \n character (as the separator, I used this instead of comma or anything else). It must save the \n characters OK because after loading the table into a DataTable object, I can split the values into arrays of string with the separator '\n' like this:

DataTable dt = LoadTable("myTableName");
DataRow dr = dt.Rows[0]; //suppose this row has the data with \n character.
string[] s = dr["myColumn"].ToString().Split(new char[]{'\n'}, StringSplitOptions.RemoveEmptyEntries);//This gives result as I expect, e.g an array of 2 or 3 strings depending on what I saved before.

That means '\n' does exist in my table column. But when I tried selecting only rows which contain \n character at myColumn, it gave no rows returned, like this:

--use charindex
SELECT * FROM MyTable WHERE CHARINDEX('\n',MyColumn,0) > 0
--use like
SELECT * FROM MyTable WHERE MyColumn LIKE '%\n%'

I wonder if my queries are wrong?

I've also tested with both '\r\n' and '\r' but the result was the same.

How can I detect if the rows contain '\n' character in my table? This is required to select the rows I want (this is by my design when choosing '\n' as the separator).

Thank you very much in advance!

Answer 1

Since \n is the ASCII linefeed character try this:

SELECT * 
FROM MyTable 
WHERE MyColumn LIKE '%' || X'0A' || '%'

Sorry this is just a guess; I don't use SQLite myself.