What's the Differences between These Two Strings?

Question

I'm baffling that I cannot find the differences between these two sets of strings, which look to be exactly the same for me. I checked for white space in between the strings, but no luck. When running below queries in SQL Management Studio, only one of them return results... Please help, thank you.

--return row
SELECT * FROM Vendors WHERE VendorCode = 'SRP  85072B'

--does not return row
SELECT * FROM Vendors WHERE VendorCode = 'SRP  85072B'

--return rows
SELECT * FROM Vendors WHERE VendorCode IN (
'ATT  60197S',
'GMI  98661A')

--does NOT RETURN rows
SELECT * FROM Vendors WHERE VendorCode IN (
'ATT  60197S',
'GMI  98661A')

They *are* identical. Or at least what you have posted (hopefully pasted). Take the time to give us a [mre]. — Thom A, Aug 11 '20 at 20:16
Are you running them both from the same window under the same database? — shawnt00, Aug 11 '20 at 20:30
Option 1: non-printable characters Option 2: [same glyph](https://stackoverflow.com/questions/36355811/linq-query-not-behaving-as-expected/36424445#36424445) different code — Lukasz Szozda, Aug 11 '20 at 20:31
@Larny They seem identical, but one returns row, the other does not — ichachan, Aug 11 '20 at 21:06
@Larnu looks like the markdown renderer renders non breaking spaces as regular spaces. The raw text is different — Martin Smith, Aug 11 '20 at 21:30
copy/paste 2 queries into an editor that can show hidden chars — urlreader, Aug 11 '20 at 21:49

Martin Smith · Answer 1 · 2020-08-11T21:39:41.180

6

One of the strings has two consecutive regular spaces, the other has a non breaking space (character 160 decimal 0xA0 hex) followed by a regular space (character 32 decimal 0x20 hex).

You can see this from copying and pasting the strings from here as I have done here https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=0dc6ccc48439c3dc27a227aa2dffb4d2

0x4154542020363031393753    
0x415454A020363031393753

edited Aug 11 '20 at 21:39

answered Aug 11 '20 at 21:22

Martin Smith

438,706
87
741
845

score 0 · Answer 2 · answered Aug 11 '20 at 20:27

0

It could be that your string or the column has special character CR or CRLF, try removing those characters in the comparison:

SELECT * 
FROM Vendors 
WHERE replace(replace(ltrim(rtrim(VendorCode)), char(13), ''), char(10), '') = replace(replace(ltrim(rtrim('SRP  85072B')), char(13), ''), char(10), '')

answered Aug 11 '20 at 20:27

iceblade

611
7
20

Thanks for your answer, but sorry to say when I run it, it returns zero row... – ichachan Aug 11 '20 at 21:05

score 0 · Answer 3 · answered Aug 11 '20 at 21:14

This code uses a tally table to split apart the two strings, convert each character to its ascii value, and return the differences.

declare
  @string1      nvarchar(200)='zdq%E^&$DGE%^#((',
  @string2      nvarchar(200)='zdq%E^&$DGx%^#((';

select ascii(SUBSTRING(@string1, t.n, 1)), t.n from dbo.fnTally(1, len(@string1)) t
except
select ascii(SUBSTRING(@string2, t.n, 1)), t.n from dbo.fnTally(1, len(@string2)) t;

score 0 · Answer 4 · answered Aug 11 '20 at 21:54

They seem similar if you run the following sql query

DECLARE @first nvarchar(max) = 'SELECT * FROM Vendors WHERE VendorCode = ''SRP  85072B''',
        @second nvarchar(max) = 'SELECT * FROM Vendors WHERE VendorCode = ''SRP  85072B'''

IF (@first = @second)
    SELECT 'Similar';

ELSE
    SELECT 'Not Similar';

If the parameters are coming from the same source you may check using the following query

DECLARE @param nvarchar(max) = 'SRP  85072B'
SELECT * FROM Vendors WHERE VendorCode like  '%'+ @param+'%'

What's the Differences between These Two Strings?

4 Answers4