Weird SQL query behavior

david weng 0
Viewed 36

I am using a select query to parse address, for some reason both like and charindex don't work. The data rows are like below:

Address
204‐101 xx CREEK DR
168‐906 xx RD
168‐906 xx RD

I tried query like

select top 10 ascii(substring(address,4,1)),
ascii('-'),
charindex(substring(address,4,1) collate SQL_Latin1_General_CP1_CI_AS,address),
charindex('-',address collate SQL_Latin1_General_CP1_CI_AS),
addr1=case when charindex('‐',address)>0 then substring(address,charindex('‐',address)+1,200) else address end

The result is like below

45  45  4   0   204‐101 xx CREEK DR
45  45  4   0   168‐906 xx RD
45  45  4   0   168‐906 xx RD

So the dash in the address has ascii code 45. But for some reason, charindex with dash returns 0, while charindex with the substring to get the dash in the address works.

I suspect it is the collation, but I did convert the addresses into my server's collation already.

Any idea? thanks！

tsql sql-server
1 Answers

They are different characters. The one in the data is NCHAR(8208) and the one in the string literal is NCHAR(45) - you should use the UNICODE function to see this not ASCII. (DB Fiddle)

Calling ASCII will implicitly convert the string to varchar and characters can be silently changed to close equivalents.

userG4j0
Related