How can I check if an SQL result contains a newline character?
SqlSql ServerSsmsSql Problem Overview
I have a varchar column that contains the string lol\ncats, however, in SQL Management Studio it shows up as lol cats.
How can I check if the \n is there or not?
Sql Solutions
Solution 1 - Sql
SELECT *
FROM your_table
WHERE your_column LIKE '%' + CHAR(10) + '%'
Or...
SELECT *
FROM your_table
WHERE CHARINDEX(CHAR(10), your_column) > 0
Solution 2 - Sql
Use char(13) for '\r' and char(10) for '\n'
SELECT *
FROM your_table
WHERE your_column LIKE '%' + CHAR(10) + '%'
or
SELECT *
FROM your_table
WHERE your_column LIKE '%' + CHAR(13) + CHAR(10) + '%'
Solution 3 - Sql
For any fellow MySQL users who end up here:
SELECT *
FROM your_table
WHERE your_column LIKE CONCAT('%', CHAR(10), '%')
Solution 4 - Sql
In Oracle, try the below command
SELECT * FROM table_name WHERE field_name LIKE ('%'||chr(10)||'%');
Solution 5 - Sql
For me, the following worked just fine in both MySQL Workbench & HeidiSQL (working with MySQL) without having to use the char() variation of \n:
SELECT * FROM table_name WHERE field_name LIKE '%\n%'
Solution 6 - Sql
SELECT *
FROM Table
WHERE PATINDEX('%' + CHAR(13) + CHAR(10) + '%', Column) > 0
Solution 7 - Sql
SELECT * FROM mytable WHERE mycolumn REGEXP "\n";
Solution 8 - Sql
select cc.columnname,right(cc.columnname,1),ASCII(right(cc.columnname,1)) ,cc.*
from table_name cc where
ASCII(right(cc.columnname,1)) = 13
here
cc.columnname=> Column name is column where you want to fine new line or Carriage return.right(cc.columnname,1)=> this will show only one char what is there at the end. usually it display only blankASCII(right(cc.columnname,1))=> here we get Acii value of that not printable value.
by seeing this ascii value we can conclude which item it is. Example , if this acsci value is 13 then Carriage return , if it is 10 then NEw line we have to use this to filter that record
cc.*=> this gives all the values from the table ( display purpose)where ASCII(right(cc.columnname,1)) = 13=> here i found all the values are 13 asci value so filtering ascii value 13
it is working for me .
Solution 9 - Sql
For any fellow Redshift users who end up here:
select * from your_table where your_column ~ '[\r\n]+'