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.
Want to try this yourself? Here is how to install AdventureWorks.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
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.