REPLACE within a CASE statement

Question

We have our team entering a dummy domain in some situations in our Email field, to signify that the entry belongs to a login ID list from another business unit (which uses a "gamertag" OR email as it's login ID). Something like this:

Retail Reps table a:

email
[email protected]
[email protected]

Users Table (provided from other business unit) b

userID
john123
[email protected]

We want to count the number of matches. Here's the CASE statement I wrote:

CASE WHEN REPLACE(a.email, '@dummydomain.com','') = b.userid THEN 1 ELSE 0 END AS [Email LoginID match]

John results in 0 (no match) and Jeff results in 1 (match), even though both are a match.

Any guidance greatly appreciated.

Answer 1

Well, I cannot see how you do the join between the tables, by this works for me:

create table e (email varchar(255))
insert into e values ('[email protected]')
insert into e values ('[email protected]')

create table u (user_id varchar(255))
insert into u values ('john123')
insert into u values ('[email protected]')
GO

4 rows affected

select 
*,
iif(user_id is null, 0, 1) as [Email LoginID match]
from e left join u
on left(e.email, charindex('@', e.email) - 1) = u.user_id
GO

email                   | user_id | Email LoginID match
:---------------------- | :------ | ------------------:
[email protected] | john123 |                   1
[email protected] | null    |                   0

db<>fiddle here