How can I check if an SQL result contains a newline character?
SELECT *
FROM your_table
WHERE your_column LIKE '%' + CHAR(10) + '%'
Or...
SELECT *
FROM your_table
WHERE CHARINDEX(CHAR(10), your_column) > 0
How to recognize if a variable contains a newline character in Oracle
A new line depends on the Operating system. In Unix based OS, it is CHR(10)
, in Windows it is CHR(13)
followed by CHR(10)
.
You could use either of the following:
LIKE '%'||chr(10)||'%'
INSTR(column_name, chr(10)) > 0
Let's look at test cases in Windows OS:
SQL
Using LIKE
SQL> WITH DATA AS(
2 SELECT 'ABC' || chr(10) || 'DEF' AS c FROM dual UNION ALL
3 SELECT 'PQR' || ' ' || 'XYZ' AS c FROM dual UNION ALL
4 SELECT 'QWE' || CHR(13) || 'RTY' AS c FROM dual UNION ALL
5 SELECT 'no_space' AS c FROM dual
6 )
7 SELECT * FROM DATA WHERE c LIKE '%'||chr(10)||'%';
C
--------
ABC
DEF
SQL>
Using INSTR
SQL> WITH DATA AS(
2 SELECT 'ABC' || chr(10) || 'DEF' AS c FROM dual UNION ALL
3 SELECT 'PQR' || ' ' || 'XYZ' AS c FROM dual UNION ALL
4 SELECT 'QWE' || CHR(13) || 'RTY' AS c FROM dual UNION ALL
5 SELECT 'no_space' AS c FROM dual
6 )
7 SELECT * FROM DATA WHERE INSTR(c, chr(10)) > 0;
C
--------
ABC
DEF
SQL>
PL/SQL
Using LIKE
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 c VARCHAR2(100);
3 BEGIN
4 c:='ABC' || chr(10) || 'DEF';
5 IF c LIKE '%'||chr(10)||'%' THEN
6 dbms_output.put_line('found chr(10)');
7 ELSE
8 dbms_output.put_line('not found');
9 END IF;
10 END;
11 /
found chr(10)
PL/SQL procedure successfully completed.
SQL>
Using INSTR
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 c VARCHAR2(100);
3 BEGIN
4 c:='ABC' || chr(10) || 'DEF';
5 IF INSTR(c, chr(10)) > 0 THEN
6 dbms_output.put_line('found chr(10)');
7 ELSE
8 dbms_output.put_line('not found');
9 END IF;
10 END;
11 /
found chr(10)
PL/SQL procedure successfully completed.
SQL>
Postgres - How to find text that contains a newline character using like
It doesn't need to be escaped as it already is escaped. It needs to be un-escaped!
You can do this by prefacing the quote mark with an E
.
select descr from description_tb where descr like E'%\n%'
Or you could just write a literal new line but there you may be at the mercy of how your client program conveys it:
select descr from description_tb where descr like '%
%'
How to check my data in SQL Server have carriage return and line feed?
You can use SQL Server's char(n)
and contains()
functions to match on field contents in your WHERE
clause.
carriage return: char(13)
line feed: char(10)
The following SQL will find all rows in some_table
where the values of some_field
contain newline and/or carriage return characters:
SELECT * FROM some_table
WHERE CONTAINS(some_field, char(13)) OR CONTAINS(some_field, char(10))
To remove carriage returns in your some_field
values you could use the replace()
function long with char()
and contains()
. The SQL would look something like:
UPDATE some_table
SET some_field = REPLACE(some_field, char(13), '')
WHERE CONTAINS(some_field, char(13))
To remove new lines you can follow up the last statement with the char(10) version of that update. How you do it all depends on what types of newlines your text contains. But, depending on where the text was inserted/pasted from the new lines may be \r\n or \n so running updates against both \r and \n characters would be safer than assuming that you're getting one version of newline or the other.
Note that if the newlines were removed and you want to retain them then you have to fix the issue at the point of entry. You can't replace or fix what has been removed so you should save the original text data in a new column that holds the original, unmodified text.
SQL query for a carriage return in a string and ultimately removing carriage return
this will be slow, but if it is a one time thing, try...
select * from parameters where name like '%'+char(13)+'%' or name like '%'+char(10)+'%'
Note that the ANSI SQL string concatenation operator is "||", so it may need to be:
select * from parameters where name like '%' || char(13) || '%' or name like '%' || char(10) || '%'
Related Topics
MySQL Select Query String Matching
Trigger Insert Old Values- Values That Was Updated
Using Input from a Text File for Where Clause
Ole Db Provider 'Microsoft.Jet.Oledb.4.0' Cannot Be Used for Distributed Queries
How to Escape Ampersand in Toad
Custom Order by to Ignore 'The'
Oracle - Best Select Statement for Getting the Difference in Minutes Between Two Datetime Columns
Stored Procedure Return Multiple Result Sets
How to Insert Multiple Rows with a Foreign Key Using a Cte in Postgres
Stratified Random Sampling with Bigquery
SQL Row_Number() Function in Where Clause Without Order By
Oracle Database Is Hanging Infinitly in Update Queries
Sql: Search for a String in Every Varchar Column in a Database
Display Parent-Child Relationship When Parent and Child Are Stored in Same Table
Assigning a Variable Inside an If Exists Clause
Counting Number of Records Hour by Hour Between Two Dates in Oracle