SQL SERVER – Search Records with Single Quotes – Part 2

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.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Previous Post
SQL SERVER – Search Records with Single Quotes
Next Post
SQL SERVER – Search Records with Single Quotes – SQL in Sixty Seconds #075

Related Posts

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.

    Reply
  • 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

    Reply

Leave a Reply

Menu