Here is my query:
SELECT
*,
LEN(Test) LENofTEST,
DATALENGTH(Test) DataLENofTEST,
CAST(Test AS VARBINARY(30)) ExtraColumn
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
These are my results :
Test COUNTR LENofTEST DataLENofTEST ExtraColumn
-----------------------------------------------------------------
100,701001207 1 13 13 0x3130302C373031303031323037
100,701001207 1 14 14 0x3130302C373031303031323037A0
123,701001207 1 13 13 0x3132332C373031303031323037
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!
6
Instead of doing
TRIM(Foo)
(Which only trims off regular spaces)
You can do
TRIM(NCHAR(0x0020) /*SP*/ + NCHAR(0x00A0) /*NBSP*/ + NCHAR(0x200B) /*ZWSP*/ FROM Foo)
To trim off a greater variety of spaces.
See the Whitespace Character Wikipedia article for other possibilities you might want to add.