Here is my query:

SELECT *, LEN(Test) LENofTEST, DATALENGTH(Test) DataLENofTEST
FROM(
SELECT TRIM(SOS1 + ',' + REPLACE(PANO20, ' ', '')) AS 'Test', COUNT(SOS1 + ',' + REPLACE(PANO20, ' ', '')) AS 'COUNTR'
FROM MyServerName.dbo.MyTableName
WHERE PANO20 LIKE '%701001207%'
GROUP BY TRIM(SOS1 + ',' + REPLACE(DUMP(PANO20), ' ', ''))
) A

Here are my results :

Test            COUNTR            LENofTEST      DataLENofTEST
100,701001207   1                 13             13
100,701001207   1                 14             14
123,701001207   1                 13             13

My issue is with the first two rows. They should be identical in length and therefore COUNTR should be 2. But for some reason, SQL is calling them different values of different lengths.

How do I go about adjusting my query so that those are counted as the same ‘Test’? Obviously I want to do it systemically and repeatably.

Thank you in advance!