Why do two identical strings have a different length but the same binary value?
Good job investigating so far. Some initial notes:


I wouldn't worry about that function from the S.O. answer.  
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

Adding GROUP BY (2nd query) doesn't change that query since it was implied in the first query ;-).
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.


Still, something is amiss. You simply cannot have different length (or different anything) with the same binary value. How was length determined: LEN or DATALENGTH? Is it possible that the value got truncated somewhere in the testing so that it then appeared to be the same?

In order to help further, we need to know two things (please update the question with the results):


The exact datatype of the department_name. Please find via:

SELECT typ.[name], col.*
FROM   sys.columns col
INNER JOIN sys.types typ
        ON typ.[user_type_id] = col.[user_type_id]
WHERE  col.[object_id] = OBJECT_ID(N'dbo.departments')
AND    col.[name] = N'department_name';

The output of the following query:

SELECT dept.department_name,
       LEN(dept.department_name) AS [name_chars],
       DATALENGTH(dept.department_name) AS [name_bytes],
       CONVERT(VARBINARY(MAX), dept.department_name) AS [name_hex]
FROM   dbo.departments dept
ORDER BY dept.department_name


Just find a set of rows for a name that looks the same but is showing duplicates via DISTINCT.


For the moment, I can say that if you have character 0 (ASCII value 0, Code Point U+0000, CHAR(0), NCHAR(0)) in your values then appearances can be deceiving since char(0) is the "null terminator" for strings. So neither it nor anything after it will be displayed, but it and everything after are still part of the string:

DECLARE @Test TABLE
(
  [Something] VARCHAR(50) COLLATE Latin1_General_CI_AI
);

INSERT INTO @Test ([Something])
VALUES ('Sometimes you' + CHAR(0) + 'feel like a nut');

INSERT INTO @Test ([Something])
VALUES ('Sometimes you' + CHAR(0) + 'feel like a nut');

INSERT INTO @Test ([Something])
VALUES ('Sometimes you' + CHAR(0) + 'don''t');

SELECT DISTINCT [Something],
       LEN([Something]) AS [Something_chars],
       DATALENGTH([Something]) AS [Something_bytes]
FROM   @Test;
/*
Something        Something_chars    Something_bytes
Sometimes you    19                 19
Sometimes you    29                 29
*/


Enjoy: Peter Paul Almond Joy &amp; Mounds - "Feel Like A Nut" (1980)



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:


these are definitely different values, and
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: &amp;nbsp;

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.


  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.