Yesterday I wrote a blog post about and it has created quite a lot of interest in the community SQL SERVER – Search Records with Single Quotes. Here are a few other alternatives which I received as a comment.
The question was how to search records with single quotes in table columns.
Suggestion from Andreas Driesen
SELECT [ProductModelID]
,[Name]
FROM [AdventureWorks2014].[Production].[ProductModel]
WHERE [Name] LIKE '%'+CHAR(39)+'%'
GO
Suggestion from Sanjay Monpara and Hitesh Shah
SELECT [ProductModelID]
,[Name]
FROM [AdventureWorks2014].[Production].[ProductModel]
WHERE CHARINDEX('''',[Name]) <> 0
GO
Suggestion from Vinod Kumar (via email)
SELECT [ProductModelID]
,[Name]
FROM [AdventureWorks2014].[Production].[ProductModel]
WHERE [Name] <> REPLACE(name, '''','')
GO
Please note that all of them gives excellent performance, so use anyone you like.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
3 Comments. Leave new
I have tried all four queries for table having 192000 total records & all are sharing same 25% time in execution plan in SSMS.
It does worrk in the Nvarchar
create table student(ID int identity(1,1) constraint [pk_student_ID] primary key,Name varchar(15), section varchar(1))
Go
insert into student values
(‘Rahul’,’B’),
(‘Ankur Juneja’,’A’),
(‘Amit Kumar’,’A’)
Go
create table Multilanguage_Name
(
MNameID int identity constraint [pk_Multilanguage_Name] primary key,
ID int constraint [fk_student_ID] references student(ID),
languageName varchar(50),
Name nvarchar(100)
)
Go
insert into Multilanguage_Name
values
(3,’Japanese’,N’アミット·クマール’)
(2,’English’,’Amit Singh’),
(2,’Japanese’,N’アミット·シン’),
(2,’Arabic’,N’أميت سينغ’)
Select * from Multilanguage_Name
where
charIndex(”,[Name])
Select * from Multilanguage_Name
where
charIndex(‘ ‘,[Name]) 0
–[Name] LIKE ‘%’
where conditions note working Like ‘%’ ‘%’, Like ‘%’+char(39)+’%’ and charindex(‘ ” ‘,Name)0
Check it
Good observation.