Difference of the handling the spaces between Oracle and SQL Server
Tags: sql, oracle, sqlserver
Published: 2022-04-05 13:04:18

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.
```sql:CreateTable(Oracle).sql
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
```
```sql:Result(Oracle).sql
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.
```sql:CreateTable(SQLServer).sql
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
```
```sql:Result(SQLServer).sql
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.