SQL select string with and without apostrophe

So, i have a DB like this

id | name

1 | students

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

2 | stu’dents

3 | student’s

If i need to search ‘students’ should have all 3 results, ignoring the apostrophes in the database

I tried something like this:

SELECT * FROM table WHERE name ILIKE 'students'

But I only have one result, because the search doesn’t ignore the apostrophes from the database.

And I tried with:

SELECT * FROM table WHERE REPLACE(name, "'", "") ilike 'students'

But return error: column "’" does not exist

Any Ideas?

>Solution :

Hi Could you please check this it’s working for me?

DECLARE @tableStudent TABLE
(
    ID INT IDENTITY(1,1),
    Name NVARCHAR(100)
)

INSERT INTO @tableStudent 
VALUES('students'),
('stu''dents'),
('student''s')

SELECT * FROM @tableStudent
WHERE REPLACE(Name,'''','') LIKE '%students%'

Result