Introduction

While working on the Database, I encountered an unique issue where I found a new line character ('\n' or Enter) at the end of my data. This leads to wrong output.

Example

I have Inserted a Customer Name with enter:

INSERT INTO Customers (CustomerName)
VALUES ('Tom
');

Solution

1. How to search

SELECT customername, INSTR(customername, chr(10))
FROM customers
WHERE INSTR(customername, chr(10)) > 0

2. How to fix

UPDATE customers
SET customername = RTRIM(customername, chr(10))
WHERE instr(customername, chr(10)) > 0

Disclaimer - Views expressed in this blog are author's own and do not necessarily represents the policies of aclnz.com