After copying entries from the Char column to the Varchar column, I cannot find a row using a similar statement in SQL Server 2014, but fine in 2003

Question

After copying entries from the Char column to the Varchar column Varchar I cannot find the row using the like operator

 Create database testDB Go USE [testDB] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[TestTable1] ( [Col_char] [char](20) NULL, [Col_nchar] [nchar](64) NULL, [Col_varchar] [varchar](64) NULL, [Col_nvarchar] [nvarchar](64) NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO insert into TestTable1 values ('Sumit1%', 'Sumit1%', 'Sumit1%', 'Sumit1%') insert into TestTable1 values ('Sumit2*', 'Sumit2*', null, 'Sumit2*') select [Col_char], LEN([Col_char]), [Col_nchar], LEN([Col_nchar]), [Col_varchar], LEN([Col_varchar]), [Col_nvarchar], LEN([Col_nvarchar]) from TestTable1 

This line gives me a search result

 select * from TestTable1 where 'sumit1' like [Col_varchar] 

Now I replace * with % and copying the [Col_char] columns to [Col_varchar]

 update TestTable1 set [Col_varchar] = Replace([Col_char], '*', '%') where [Col_char] like '%2%' select * from TestTable1 select * from TestTable1 where 'sumit1' like [Col_varchar] -- this line is not showing any results select * from TestTable1 where 'sumit2' like [Col_varchar] select Len(Replace([Col_char], '*', '%')), Len(Replace([Col_varchar], '*', '%')), * from TestTable1 

Answer 1

When you have SET ANSI_PADDING ON , CHAR(20) will always have 20 characters, filling the right side with spaces.

When you convert this to varchar, you still have 20 characters, so your Col_varchar value is actually "Sumit2% " , so you are looking for a line that starts with Sumit2 but also has a bunch of spaces at the end

if you replace the value with

 UPDATE TestTable1 SET [Col_varchar] = RTRIM(REPLACE([Col_char],'*','%')) WHERE [Col_char] LIKE '%2%' 

It should work for you.

Information about ANSI_PADDING https://msdn.microsoft.com/en-us/library/ms187403.aspx