Finding ASCII values ​​in sql server table

Question

I have a table that needs to be cleared with one of the fields. The basic structure of the table is as follows:

create table testComments(id int, comments text) 

In the comments column, on some lines there were many “carriage returns” (char (13)) and “line feed” (char (10)). If there is more than one group in each line, I need to modify it. The main select statement I have is the following:

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

Results will be found in this query.

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

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

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

The query will only return the number of records from 1 for the above data. Any idea how I can modify the request to return invoice 2?

Answer 1

Using the code that you provided to us, your request should work correctly. So the details seem different, and I suspect that the GolezTrol comment is on the right track - some CRLF pairs are actually just personal CR or LF characters.

Try the following:

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

Answer 2

The query will only return the number of records from 1 for the above data. Any idea how I can modify the request to return invoice 2?

I think you misunderstand something basic. The COUNT function returns the number of all rows returned. In your example, it still returns row one .

So, regardless of whether you have this:

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

Or that:

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

COUNT will still return 1! (if you have one entry with this line)

EDIT: If you literally want to count the characters for each line, you are here:

 SELECT LEN(REPLACE(myColumn, 'yourCharacter', '')) FROM ... 

Answer 3

Try using a virtual table. I added differences in the length of the comment field and in the comment field after replacing the characters you are looking for with empty lines.

 SELECT SUM(DIF) FROM ( select ( LEN(comments) - LEN( REPLACE( REPLACE ( comments, char(13), ''),char(10),'') ) ) AS DIF from #testComments ) as VT;