I have a MySQL DB table where a text column has some values which seem to be only CR and LF control characters (the value is just the line break).

I need a query which will identify all such rows. I tried something like this

SELECT * FROM mytable WHERE mycolumn REGEXP "\r\n";

from here. But that didn't work. I guess I just need the correct regex in my case. Any suggestions would be greatly appreciated.

3 Answers

0
Community On

I inserted a col with line breaks, i could retrieve it with foll SQL

mysql> select lat from TEST_INSERT where lat regexp '.*[\n]';
+--------------+
| lat          |
+--------------+
|  xx 
yy
zz

 |
+--------------+
1 row in set (0.00 sec)
0
Rick James On
WHERE col = "\r\n"

will check for that column having only a Windows-type line break.

WHERE col = "\n"

for unix-style.

0
Community On

Is it what you are looking for???

mysql> select lat from TEST_INSERT where lat regexp '^\n+$';
+-------+
| lat   |
+-------+
| 




 |
+-------+
1 row in set (0.00 sec)