DEV Community

loading...

Discussion on: SQL: Where spaces may not matter

Collapse
funkybob profile image
Curtis Maloney

I have to ask... which DBMS did you try this with?
Intrigued, I tested your sample 'x' = 'x ' query on Postgres 10.4 and it did not match.

Collapse
antogarand profile image
Antony Garand Author

MySql, MSSql and oracle work from my experience

Collapse
martinmarques profile image
Martín Marqués

I'd be more than surprised if Oracle would not distinguish 'admin' from 'admin ' on insert, but I don't have an Oracle server handy to test. I can assure that this doesn't happen with Postgres (tested on 9.4 - 10)

Very interesting findings. I hope t never bump into issues like that :)

Thread Thread
antogarand profile image
Antony Garand Author

I'm not that familiar with Oracle but based on this sqlFiddle, I can confirm to you that this does work:

sqlfiddle.com/#!4/c0be1c/19813

Thread Thread
scottishross profile image
Ross Henderson

Ran in Oracle SQLDev:

begin
    if 'admin' = 'admin ' then dbms_output.put_line(1);
    else dbms_output.put_line(0);
    end if;
end;

Result: 1

It is correct that Oracle automatically trims leading and trailing spaces.

Edit: I should mention that this is on 18.2