Find gender of employees from their SSN

Question

I should find gender of employees from their (SSN).and theory is , if the last number of SSN is even (0,2,4,6,8) gender is woman or/else if the last number of SSN is odd (1,3,5,7,9) gender is man.

SSN of employees look like this in my DataBase ,for ex : 1111112020 or 22222231 - and column name is XX and datatype is nvarchar(30).

The sql i wrote look like this to find out gender of employees and when i executed query i get NULL. Can someone please point me in the right direction. Thanks.

DECLARE @Mand char(5) = '1,3,5,9,7'
DECLARE @Woman char(5) = '0,2,4,6,8'
DECLARE @Gender char (1)

SELECT (
CASE 
WHEN  right(rtrim(SSN),1) = @Mand THEN 'MAN'
WHEN right(rtrim(SSN),1) = @Woman THEN 'Woman'
ELSE NULL 
END) as gender
FROM U  
WHERE I = XXX

Answer 1

You cannot use strings and in that way. Just use the explicit list in the query:

SELECT (CASE WHEN right(rtrim(SSN), 1) IN ('1', '3', '5', '7', '9') THEN 'MALE'
             WHEN right(rtrim(SSN), 1) IN ('2', '4', '6', '8', '0') THEN 'FEMALE'
        END) as gender
FROM dbo.Users  
WHERE CustomerId = 214;

Alternatively, you could use LIKE:

SELECT (CASE WHEN rtrim(SSN) LIKE '%[13579]' THEN 'MALE'
             WHEN rtrim(SSN) LIKE '%[24680]' THEN 'FEMALE'
        END) as gender
FROM dbo.Users  
WHERE CustomerId = 214;

I should note that I am not aware that gender is assigned this way for US social security numbers.