TomThomson (5/16/2016)
The trick for counting CHAR(0) in a string is to count the number of length one substrings that are equal to CHAR(0). Gloriously inefficient, of course, but I don't know another way of doing it.
As shown in my previous post above, using a _BIN2 Collation via the COLLATE clause (for columns that are not set to a binary Collation and when using literals while not in a database that has a binary default Collation) would allow you to handle CHAR(0) without it having any "special" behaviors. Reworking the example code from that prior post (to focus only on the counting of CHAR(0) query), we get:
SET NOCOUNT ON;
DECLARE @STR_WITH_ZERO_CHAR VARCHAR(20) = '123' + CHAR(0) + '45' + CHAR(0)
+ '678' + CHAR(0) + '90';
SELECT LEN(@STR_WITH_ZERO_CHAR)
- LEN(REPLACE(@STR_WITH_ZERO_CHAR COLLATE Latin1_General_100_BIN2, CHAR(0), ''))
AS [CountOfChar0]
The query above returns: 3.
In the case of using a VARCHAR column to store the hex bytes of encrypted or hashed values, you might as well set the Collation of that column to Latin1_General_100_BIN2 which will allow the comparisons to be even more efficient than specifying COLLATE per each query.
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR