SQL Handle special characters in strings during query SSMS

Question

I have a database Customers. Columns as below

ID    CompanyName
1      Bon app'
2      %Benguir$
3      ABB?

DELETE FROM Customers WHERE CompanyName = 'Bon app\''

I wish to say delete records where CompanyName is Bon app' . Notice the ' at the end. I wish to do the same for %Benguir$ and ABB?. Basically I want to know how to handle strings with special characters in SQL while using query

The only character you need to escape is a single quote, which you escape by doubling it. The rest just work. — Dale K, Apr 22 '20 at 10:06

score 0 · Accepted Answer · answered Apr 22 '20 at 10:22

If I understand correct, you should only have issue with your first record with Bon app'. Please check from the below script how you can select all 3 records. This should help you to delete specific records.

DEMO HERE

WITH your_table(ID,CompanyName)
AS
(
    SELECT 1,'Bon app''' UNION ALL
    SELECT 2,'%Benguir$' UNION ALL
    SELECT 3,'ABB?'
)

SELECT * FROM your_table WHERE CompanyName = 'Bon app'''
UNION ALL
SELECT * FROM your_table WHERE CompanyName = '%Benguir$'
UNION ALL
SELECT * FROM your_table WHERE CompanyName = 'ABB?'

I just demonstrate the selection for different Word. You can use above conditions and logic in your delete script.

SQL Handle special characters in strings during query SSMS

1 Answers1