SQL: query for finding and removing new line

trevor1940
trevor1940 used Ask the Experts™
on
Hi

I have a movie database some how I've got some file path with a new line character

Like this
C:\Path\To\MyFile.mp4\n

Open in new window


How do I locate these?

this caused
Msg 402, Level 16, State 1, Line 12
The data types varchar and char are incompatible in the '|' operator.

SELECT * FROM "file" WHERE path like '%' | char(13) | '%' or path like '%' | char(10) | '%';

Open in new window


Once I've found then I'll need to remove the "\n";
But I think there is going to be duplicate
and the file . id is a foreign key


BTW It is a MS SQL database
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
String concatenation in SQL Server is a "+" not a "|".

Author

Commented:
OK thanx that handled the SELECT

Can you tell me what the syntax is for removing the "\n"?
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
If it is literally the string '\n', then try this:
update myfile set path=substring(path,1,len(path)-2) where substring(path,len(path)-1,2) = '\n';

Open in new window


My test case:
https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=f75ddacfc24b7eae1fc5f5625c29dc38
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
UPDATE "file"
SET path = REPLACE(REPLACE(path, CHAR(10), ''), CHAR(13), '')
WHERE path LIKE '%[' + CHAR(10) + CHAR(13) + ']%'

Author

Commented:
Thanx for your help
I'm about to ask a follow up Question on how to remove the duplications

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial