REPLACE within a CASE statement

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:

Users Table (provided from other business unit) b

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.

>Solution :

This seems to work:

SELECT u.UserID, count(*) as [Email LoginID match]
FROM Users u 
INNER JOIN RetailReps rr on u.UserID = Replace(rr.email,'@dummydomain.com','')
GROUP BY u.UserID

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=1569a6474024e04052ddcb4c4b14d23b

fwiw, you should probably be using a variant of example.com or dummydomain.test as your dummy domain.