How to handle special charters in query

[fawadkhalil]

Hi

Its in fact easy to handle special characters like "%" in query, with just a bit of research one can found solution for this.

Today when i checked events for my server i found that one error message and that was because a user searched for " l'atelier " on my site, with this word the query will become like:

Select * from Category  where MDelete =0  
    And CategoryName Like '%l'atelier%'

where as for a simple word like e.g "lipstick" the query works fine and will be:

Select * from Category  where MDelete =0  
    And CategoryName Like '%lipstick%'

Any body please tell me how can i handle this??

[BitBlt]

For your particular scenario, SQL Server will properly evaluate a single quote if you put two quotes in a row like so:

Select * from Category  where MDelete =0  
    And CategoryName Like '%l''atelier%'

I suggest that you use some sort of REPLACE function in your code that will replace any occurrence of a single quote with two single quotes.

For a more robust solution, you may want to go to the Transact SQL help file and investigate the concept of the "Escape" character. This will help you deal with other special characters that might be mis-evaluated in your LIKE clause.

Hope this helps! Good luck!

[adam_k]

You could use the QUOTENAME function (Read about it here: http://msdn.microsoft.com/en-us/library/ms176114.aspx )
for strings up to 128 characters.