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

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

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

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.

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

Hope this helps.

That's:

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

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.

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 '%''%'

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

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.