SQL checking the last letter in string / AKA LIKE strange behaviour

Question

I know it was already answered but it doesn't work for me.

So quick introduce:

I have table called swimmers. I'll insert new record to it which fulfils all columns:

INSERT INTO swimmers(id, first_name, last_name, age, gender)
VALUES(9,'Maria','Spolsky',34,'Female');

Now I want to find records in swimmers table which first_name DOES NOT end with letter a and where gender is Female

So I wrote SQL query:

SELECT first_name, last_name
FROM swimmers
WHERE first_name NOT LIKE '%a' AND gender = 'Female'

But it does return Maria which we just added. It basically returns every female

I'm using ORACLE iSQL plus.

EDIT:

I tried to use substr(first_name, -1) = 'a' but it turned out that -1 is empty, because I use varchar(20) and names are usually smaller than 20 characters

EDIT2:

I tried to find issue in data type. I used char(20) for first_name.

I changed it into varchar2(20):

ALTER TABLE swimmers 
MODIFY first_name varchar2(20);

but didn't solve the issue

EDIT 3:

Changing NOT LIKE to WHERE first_name LIKE '%a' returns no rows. I believe issues lies inside data type and empty spaces from the end of the first_name to the end of reserved space for string (names has ~10 characters and I use `varchar2(20))

Answer 1

Appearantly oracles is adding spaces behind the name and that's why '%a' doesn't work, either change your column definition to a varchar (so oracle doesn't add the extra spaces) or trim the spaces out of your name like this:

SELECT first_name, last_name
FROM swimmers
WHERE NOT trim(first_name) LIKE '%a' AND gender = 'Female';