Return to Answer

REPLACE(@InputParam, NCHAR(0x00A0) COLLATE Latin1_General_100_BIN2, N'');

To be clear, all of that work messing with VARBINARY and VARCHAR, etc is unnecessary.

REPLACE(@InputParam, NCHAR(0x00A0) COLLATE Latin1_General_100_BIN2, N'')

To be clear, all of that work messing with VARBINARY and VARCHAR, etc is unnecessary.

But this won't work long term as users can update this table and I would need to adjust every query to do a replacement in the WHERE clause.

True, updating every WHERE clause is not a workable solution. This is why you need to sanitize the input on the way in. There are a limited number of entry points for the data (INSERT / UPDATE procs for the UI, possibly some ETL processes), so it shouldn't be that bad. You can request that the developers strip out "bad" characters before they call the stored procedure(s), but there is no guarantee that they will, or that new code will, or that things won't change later, or that they will be able to fix ETL processes, etc.

TEMPORARY UPDATE (WAITING FOR QUESTION TO BE UPDATED WITH QUERY OUTPUT):

Based on this info from a comment:

I used both LEN and DATALENGTH to perform the check. For two exact same strings I got (20,40) and (21,42) respectively.

it is clear that:

1. these are definitely different values, and
2. the datatype of the column is NVARCHAR (since DATALENGTH is twice LEN)

That means that the 0xA000 value is a single, UTF16LE character. Due to being Little Endian (bytes in reverse order), the actual Code Point is U+00A0. That character is:

No-Break Space

As in our favorite HTML character:  

All you need to do is remove those characters on the way into the DB, using:

REPLACE(@InputParam, NCHAR(0x00A0) COLLATE Latin1_General_100_BIN2, N'');

For example:

SELECT CONVERT(VARBINARY(MAX),
      REPLACE(N'test' + NCHAR(0x00A0), NCHAR(0x00A0) COLLATE Latin1_General_100_BIN2, N'')
    );

To be clear, all of that work messing with VARBINARY and VARCHAR, etc is unnecessary.

1. I wouldn't worry about that function from the S.O. answer.

2. RTRIM and LTRIM only trim spaces, not white-space in general:

    SELECT RTRIM('A    ') + 'a';
    -- Aa
   
    SELECT RTRIM('A    ' + CHAR(9)) + 'a'; -- CHAR(9) = tab
    -- A        a
   

3. Adding GROUP BY (2nd query) doesn't change that query since it was implied in the first query ;-).

1. I wouldn't worry about that function from the S.O. answer.

2. RTRIM and LTRIM only trim spaces, not white-space in general:

    SELECT RTRIM('A    ') + 'a';
    -- Aa
   
    SELECT RTRIM('A    ' + CHAR(9)) + 'a'; -- CHAR(9) = tab
    -- A        a
   

3. Adding GROUP BY (2nd query) doesn't change that query since it was implied in the first query ;-).

4. Whether 0xA000 is 2 VARCHAR characters or 1 NVARCHAR character, there does not seem to be any special behavior with this sequence of bytes for either datatype, using either Latin1_General_CI_AI or Latin1_General_100_CI_AI.