Search for ASCII values in sql server table

Question

I have table that one of the fields needs to be cleaned up. The basic table structure is as follows:

create table testComments(id int, comments text)

In the comments column some of the rows had to many "carriage returns" (char(13)) and "line feed" (char(10)). If there is more then one grouping per line I need to be able to modify it. The basic select statement that I have so far is a follows:

  select count(id)
  from testComments
  where comments like('%' +  char(13) + char(10) + char(13) + char(10) + '%')

This query will find the results

"This is a entry in the testComments crlf
crlf
In the comments field that works"

Although the query will not find the results if the comment is listed as follows:

"This is an entry in the testComments crlf
crlf
crlf
That will not work"

The query will only return a count of 1 entry for the above data. Any idea how I can change the query to return a count of 2?

Answer 1

Using the code you gave us, your query should work properly. So the details appear to be different, and I suspect that GolezTrol's comment is on the right track -- some of the CRLF pairs are really just solo CR or LF characters.

Try this:

select count(id)
  from #testComments
  where comments like('%' +  char(13) + char(10) + char(13) + char(10) + '%')
     or comments like('%' +  char(10) + char(10) + '%')
     or comments like('%' +  char(13) + char(13) + '%')