Double carriage returns in MySQL field

MrMHarris
MrMHarris used Ask the Experts™
on
I have recently been shown some code by experts that searches for carriage returns or breaks in a MySQL database:

SELECT *
FROM records
WHERE description LIKE CONCAT('%',CHAR(10) , CHAR(10) , '%')
LIMIT 0,90000;

Open in new window


I have also tried the CHAR(13) and

SELECT *
FROM records
WHERE description LIKE '%\r\r%'

Open in new window

 also trying \n\n as well

When I do it with a single break, it gives me lots of returns but none with the double returns.  I know double breaks exist in the db because when rendered in HTML they appear:

<p>...... - lovely old houses, narrow streets, a lovely church etc. <br><br><br></p>

Open in new window


Any ideas how to identify which records have double breaks or why the above code  is not picking these multiple breaks up?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Dave BaldwinFixer of Problems
Most Valuable Expert 2014

Commented:
"<br>" is not the same as "\r\n".   If "<br>" is what is in the records, you have to search for it as text '<br>'.

Author

Commented:
Sorry Dave, I have also searched for "<br>" in the text and "<br />" - nothing.

There is no HTML in the text. The returns are being translated as "<br>" in the HTML.

See the CMS and text file of query. See the spaces below etc.
Also a clip from exported query in a text file.
Screen-shot-2011-08-04-at-18.02..png
Dave BaldwinFixer of Problems
Most Valuable Expert 2014

Commented:
No, something else may be translating them like the 'nl2br()' in PHP, but HTML specifically never translates or displays returns or linefeeds.  You said 'CMS' which implies a server side language which could do that.

Author

Commented:
Cheers Dave, maybe I am missing a trick here, the returns are definitely in the db .  This quick test shows:

Create data with 5 carriage returns;
 Entering returns
OK, they seem to be inserted...
 Returns written to field
Now I'll search for a record where there is a return;
 Search for a return
It finds the return(s)  Good, now we'll look for multiple returns that we created, unfortunately, as the results show - nothing;
 Search results for multiple returns
So how do I find occurrences of the multiple returns that are there in the db?
Dave BaldwinFixer of Problems
Most Valuable Expert 2014

Commented:
It looks like there are possibly other characters in there like a space or something.  Bring up that one record and highlight the text and see what you find.

Author

Commented:
Doesn't look like it, when I entered the data I simply pressed the c/r key 5 times.  I want to be able to replicate the a successful execution of the query, but cannot!
 No erroneous space
Fixer of Problems
Most Valuable Expert 2014
Commented:
It may be doing a Windows EOL which is \r\n.  Search for '%\r\n%' and see what you get.
Thanks Dave, that got me to the solution!

To find multiple occurrences of carriage returns in a MySQL db, use the following:

SELECT *
FROM tablename
WHERE fieldname LIKE '%\r\n\r\n\r\n%'


This will find all occurrences of 3 carriage returns; \r\n\r\n for 2 etc.

To deduplicate or replace 3 with 2 use:

UPDATE tablename SET fieldname=REPLACE(fieldname,'\r\n\r\n\r\n','\r\n\r\n')

Where the carriage returns are at the end of the field I did this (as you cannot use wildcards in REPLACE function)

UPDATE tablename SET fieldname=REPLACE(fieldname, fieldname, TRIM(BOTH '\r\n' FROM fieldname))

Author

Commented:
It got me to the solution!

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