March 25, 2015 at 9:11 am
Good morning!
To begin with I don't know for sure what version of SQL Server this code code is running against but i think it is 2012. If it turns out to be important i can get the version.
I have a CASE expression that looks like this:
SELECT
CASE
WHEN FileCreditedTo IN ('Debbie Bodwell', 'Debra Nancy Bodwell') THEN 'AUB'
WHEN FileCreditedTo IN ('Diane Watson','Diane Watson','Michael G. Wilson','Michael G. Wilson','Michael Wilson') THEN 'AUG'
...
ELSE
'NA'
END
It seems that if a name has two internal spaces (i.e. 'Diane Watson'), it is not matched and falls through to 'NA'. I have searched these two space names for weird ASCII characters but found none.
Any idea why they are not matched?
Thank you!
March 25, 2015 at 9:18 am
Those additional spaces seem to have been collapsed/removed somehow.
Are you questioning the results of the following?
if 'Diane Watson' = 'Diane Watson'
select 'Matched'
else
select 'No match';
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
March 25, 2015 at 9:49 am
Nope. The problem seems to be that "two space name does not equal two space name" at least in the context of CASE expression. So that
if 'Diane Watson' = 'Diane Watson'
select 'Matched'
else
select 'No match'
would return 'No match'. I know it sounds crazy but it seems to be happening.
Thanks for your help
March 25, 2015 at 9:55 am
Hmm. What about this?
declare @Name varchar(50) = 'Diane Watson';
select Name = @Name, Result = case when @Name = 'Diane Watson' then 'One Space'
when @Name = 'Diane Watson' then 'Two spaces'
else 'Other'
end;
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
March 25, 2015 at 10:16 am
It's possible that's a tab not a space between the names.
March 25, 2015 at 11:47 am
I though of the tab possibility but when I use the ASCII() function there is no tab.
Thanks everyone for your responses. This sql was written by me but is running on a third party server after the data is passed over a web service. I think something is happening when the data is serialized by the service that may be causing this because i cannot reproduce it locally.
I am talking to the third party now.
March 25, 2015 at 2:10 pm
Maybe it's a case-sensitive db and the literal's case doesn't match the case stored in the table??
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply