Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

After copying records from a Char column to a Varchar column, I'm unable to find the row using like statement in SQL Server 2014 but fine in 2003

After copying records from a Char column to a Varchar column, I'm unable to find the row using like statement

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 is giving me the search result

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

Now I am replacing * with % & copying [Col_char] to [Col_varchar] column

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 
like image 784
Sumit khare Avatar asked Dec 14 '15 15:12

Sumit khare


People also ask

What is the use of @@ error in SQL Server?

Using @@ERROR to conditionally exit a procedure. The following example uses IF...ELSE statements to test @@ERROR after an DELETE statement in a stored procedure. The value of the @@ERROR variable determines the return code sent to the calling program, indicating success or failure of the procedure.

How do you fix error string or binary data would be truncated?

Solution. To avoid this error and to insert the string with truncation, use the ANSI_WARNINGS option. On setting ANSI_WARNINGS to OFF, the error message will not be displayed and the data will be automatically truncated to the length of the destination column and inserted.

What is the error in MySQL syntax?

During application update an error message containing "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ..." appears in the log. It means your database is outdated and it can't work with the request our application sends to it.


1 Answers

When you have SET ANSI_PADDING ON a CHAR(20) will always be 20 characters by padding the right side with spaces.

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

if you replace the value using

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

it should work for you.

Info on ANSI_PADDING https://msdn.microsoft.com/en-us/library/ms187403.aspx

like image 109
JamieD77 Avatar answered Sep 20 '22 19:09

JamieD77