I conducted an experiment about the handling the spaces between Oracle and SQL Server because noticed the difference when using Oracle for my work.
(Oracle's version is Oracle 19c and SQL Server's verstion is SQL Server 2019 as these had already been installed in my PC.)
Experiment
Oracle
I conducted an experiment under following table and data.
CREATE TABLE CompareTestTable
(
Seq NUMBER
,ColCHAR CHAR(10)
,ColVARCHAR VARCHAR2(10)
);
INSERT INTO CompareTestTable VALUES( 1, 'aaa', 'bbb' ); -- No space
INSERT INTO CompareTestTable VALUES( 2, 'aaa ', 'bbb ' ); -- A space after a string
INSERT INTO CompareTestTable VALUES( 3, ' aaa', ' bbb' ); -- A space before a string
INSERT INTO CompareTestTable VALUES( 4, ' aaa ', ' bbb ' ); -- Spaces after and before a string
select * from CompareTestTable where ColCHAR = 'aaa'; -- Hit SEQ=1,2 records
select * from CompareTestTable where ColCHAR = 'aaa '; -- Hit SEQ=1,2 records
select * from CompareTestTable where ColCHAR = ' aaa'; -- Hit SEQ=3,4 records
select * from CompareTestTable where ColCHAR = ' aaa '; -- Hit SEQ=3,4 records
select * from CompareTestTable where ColVARCHAR = 'bbb'; -- Hit SEQ=1 record
select * from CompareTestTable where ColVARCHAR = 'bbb '; -- Hit SEQ=2 record
select * from CompareTestTable where ColVARCHAR = ' bbb'; -- Hit SEQ=3 record
select * from CompareTestTable where ColVARCHAR = ' bbb '; -- Hit SEQ=4 record
The space after string is seemed to be ignored when colomn type is Char.
However, in the event of Varchar2, the space after or before string isn't seemed to be ignored.
The detail is following
https://docs.oracle.com/cd/B13789_01/server.101/b10759/sql_elements002.htm#:~:text=Nonpadded%20Comparison%20Semantics,-Oracle%20compares%20two&text=If%20two%20values%20of%20different,the%20values%20are%20considered%20equal.
SQL Server
As the same case for Oracle, I conducted an experiment under following table and data on SQL Server.
CREATE TABLE CompareTestTable
(
Seq INT
,ColCHAR CHAR(10)
,ColVARCHAR VARCHAR(10)
)
INSERT INTO CompareTestTable VALUES( 1, 'aaa', 'bbb' ); -- No space
INSERT INTO CompareTestTable VALUES( 2, 'aaa ', 'bbb ' ); -- A space after a string
INSERT INTO CompareTestTable VALUES( 3, ' aaa', ' bbb' ); -- A space before a string
INSERT INTO CompareTestTable VALUES( 4, ' aaa ', ' bbb ' ); -- Spaces after and before a string
select * from CompareTestTable where ColCHAR = 'aaa'; -- Hit SEQ=1,2 records
select * from CompareTestTable where ColCHAR = 'aaa '; -- Hit SEQ=1,2 records
select * from CompareTestTable where ColCHAR = ' aaa'; -- Hit SEQ=3,4 records
select * from CompareTestTable where ColCHAR = ' aaa '; -- Hit SEQ=3,4 records
select * from CompareTestTable where ColVARCHAR = 'bbb'; -- Hit SEQ=1,2 records
select * from CompareTestTable where ColVARCHAR = 'bbb '; -- Hit SEQ=1,2 records
select * from CompareTestTable where ColVARCHAR = ' bbb'; -- Hit SEQ=3,4 records
select * from CompareTestTable where ColVARCHAR = ' bbb '; -- Hit SEQ=3,4 records
The result of Char is the same as Oracle.
However, in the event of Varchar, the result is different between Oracle and SQL Server, the spaces after or before string is seems to be ignored.
Therefore, it should be note when using varchar because of the difference between Oracle and SQL Server as such above.
Top comments (0)