How do I search for names with apostrophe in SQL Server?

Question

SELECT *
  FROM Header
 WHERE (userID LIKE [%'%])

Monocoque

Answer 1

Double them to escape;

SELECT *
  FROM Header
 WHERE userID LIKE '%''%'

Disorganization

what if the value is stored in a variable ? – Juryrig

Answer 2

SELECT     *
FROM Header WHERE (userID LIKE '%''%')

Furtherance

@TalhaIrfan The double-to-escape is not explained. – Marty

Answer 3

SELECT *   FROM Header  WHERE userID LIKE '%' + CHAR(39) + '%' 

Sorghum

Answer 4

SELECT * FROM TableName WHERE CHARINDEX('''',ColumnName) > 0 

When you have column with large amount of nvarchar data and millions of records, general 'LIKE' kind of search using percentage symbol will degrade the performance of the SQL operation.

While CHARINDEX inbuilt TSQL function is much more faster and there won't be any performance loss.

Reference SO post for comparative view.

Ambitendency

Answer 5

That's:

SELECT * FROM Header 
WHERE (userID LIKE '%''%')

Passible

Answer 6

select * from Header where userID like '%''%'

Hope this helps.

Spalato

Answer 7

First of all my Search query value is from a user's input. I have tried all the answers on this one and all the results Google have given me, 90% of the answers says put '%''%' and the other 10% says a more complicated answers.

For some reason all of those did not work for me.

How ever I remembered that in MySQL (phpmyadmin) there is this built in search function so I tried it just to see how MySQL handles a search with an apostrophe, turns out MySQL just escaping apostrophe with a backslash LIKE '%\'%' so why just I replace apostrophe with a \' in every user's query.

This is what I come up with:

if(!empty($user_search)) {
        $r_user_search = str_ireplace("'","\'","$user_search");
        $find_it = "SELECT * FROM table WHERE column LIKE '%$r_user_search%'";
        $results = $pdo->prepare($find_it);
        $results->execute();

This solves my problem. Also please correct me if this is still has security issues.

Walrus

Answer 8

Brackets are used around identifiers, so your code will look for the field %'% in the Header table. You want to use a string insteaed. To put an apostrophe in a string literal you use double apostrophes.

SELECT *
FROM Header WHERE userID LIKE '%''%'

Bodywork

Answer 9

Compare Names containing apostrophe in DB through Java code

String sql="select lastname  from employee where FirstName like '%"+firstName.trim().toLowerCase().replaceAll("'", "''")+"%'"

statement = conn.createStatement();
        rs=statement.executeQuery(Sql);

iterate the results.

Quantic

Please try to explain for OP to understand – Satiny