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))

I used to be char(20). Now I changed it into varchar(20).Both cases don't work — Filip Bartuzi, Jun 16 '15 at 08:54
the problem is that oracle saves a bunch of spaces behind the name appearantly until you get to max chars, I did a fiddle with char(20) as column type and it works if i do the like condition like this: LIKE '%a ' (15 spaces behind a), having the same problem with varchar(20) doesn't make sense though — Jeremy C., Jun 16 '15 at 08:56
[Check this sqlfiddle.](http://sqlfiddle.com/#!4/1d8e1/1) And you can cross check the table definitions and see what's different in your setup. Just mean to point out that this isn't an Oracle11g problem, but something with your setup. — CodeNewbie, Jun 16 '15 at 08:56
though it's still weird that you get the same issue with a varchar, might be because you didn't re add the row that it wasn't working? did you remove the name maria etc... from the table and re add it after changing to varchar? if you didn't then it probably still had the old value with the spaces in the varchar field and that is why it still wasn't working — Jeremy C., Jun 16 '15 at 09:05

score 2 · Accepted Answer · answered Jun 16 '15 at 08:58

2

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';

answered Jun 16 '15 at 08:58

Jeremy C.

2,195
1
9
24

Trim works! Want to alter table and modify `varchar2(20)` into `varchar(20)` but it seems to be tricky thing... :/ – Filip Bartuzi Jun 16 '15 at 09:04
alter table swimmers modify first_name varchar(20); – Jeremy C. Jun 16 '15 at 09:07
2

but the problem is your old rows will probably still be saved with the spaces behind the name so also run this query: UPDATE swimmers SET first_name = trim(first_name) – Jeremy C. Jun 16 '15 at 09:09
you also might want to alter and update last_name while you are at it – Jeremy C. Jun 16 '15 at 09:36
it isn't that easy. `alter table swimmers modify first_name varchar(20)` doesn't change `varchar2` to `varchar`. It is still `varchar2`. I think it needs explicit conversion. – Filip Bartuzi Jun 16 '15 at 14:43
1

I thought your tables were char though?, if your tables are already in varchar2 then just do the update and not the alter table, they already are varchar, just trim the values so the spaces are removed, oracle is working on removing varchar entirely in future versions so just leave the varchar2 – Jeremy C. Jun 17 '15 at 06:53

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

1 Answers1