May 3, 2012 at 3:35 pm
hi,
i'm stumpped as to why an EXCEPT statement is still seeing a difference between two text values that are identical. the selection table contains an nvarchar field. the comparison table contains a set of varchar fields that are concatenated into a single field.
in my tests i've found that the DATALENGTH of the two fields are identical. i've tried explicitly casting the return columns in each table to be of the same type (i.e. both varchar(i) or nvarchar(i)). i've even tried explicitly defining the collation of each column to match as well. no dice...
select ltrim(rtrim(streetAddress))
from tbl1
except
select
isnull(ltrim(rtrim(street1)),'')
+ ...
+ isnull(ltrim(rtrim(street5)),'') as streetAddress
from tbl2
executing that will return
streetAddress
==============================
123 I Shouldn't Be Here St, Sometown, MA 12345
executing each query independently will return
streetAddress
==============================
123 I Shouldn't Be Here St, Sometown, MA 12345
streetAddress
==============================
123 I Shouldn't Be Here St, Sometown, MA 12345
i'm not sure what else i can test. as far as i can tell, they're identical.
thoughts? thanks!
- d
May 3, 2012 at 3:43 pm
turns out the spaces in between the words in tbl1 were char(10) values, which are not the same as ' ' characters. a replace statement in the selection query did the trick...
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply