MS SQL Anomalies 3

For those who are still using ancient data types such as CHAR be careful if you are also using REPLACE function in MS SQL servers:

Let’s test VARCHAR and CHAR fields on a simple table like the following:

CREATE TABLE [dbo].[test] (
  [varchar5] varchar(5) COLLATE Latin1_General_BIN NOT NULL,
  [char5] char(5) COLLATE Latin1_General_BIN NULL,
  PRIMARY KEY CLUSTERED ([varchar5])
)
ON [PRIMARY]
GO

select varchar5, char5
, right(char5, 3) as right3
, replace(right(char5, 3),' ', '-') as replace_space_of_right3
, replace(char5,' ', '-') as replace_space
from test

The output is:

(version MS SQL Server 2000)

As usual, Microsoft developers are full of surprises!