CODEWITHSUNDEEP
sqlsql-serverstring
ichachan
ichachan

Reputation: 667

What's the Differences between These Two Strings?

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')

Upvotes: 0

Views: 182

Answers (4)

Gnyasha
Gnyasha

Reputation: 684

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+'%'

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453243

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

Upvotes: 6

SteveC
SteveC

Reputation: 6015

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;

Upvotes: 0

iceblade
iceblade

Reputation: 641

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), '')

Upvotes: 0

Related Questions