Search for ASCII values in sql server table

sql, sql-server

Solution

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) + '%')

Problem

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?

Original source