0

I have a where clause that uses a string,

Where 
    pm.Alias = 'Toys'R'Us France'

However part of the string uses single quotation marks, 'R'

How do i wrap up the whole string to pass through into my Where clause

I cannot use:

Where 
    pm.Alias = 'Toys''R''Us France'

As i need the whole string encased, as i will use this in Excel to pass this as a paramter into my query

PeterH
  • 837
  • 2
  • 13
  • 32
  • 5
    Double them. I.e. `'Toys''R''Us France'`. – jarlh Dec 07 '17 at 10:53
  • 4
    Possible duplicate of [How do I escape a single quote in SQL Server?](https://stackoverflow.com/questions/1586560/how-do-i-escape-a-single-quote-in-sql-server) – Ullas Dec 07 '17 at 10:54
  • Specify 2 consecutive single quotes within the string literal instead of 1. `pm.Alias = 'Toys''R''Us France'` – Dan Guzman Dec 07 '17 at 10:54
  • i need to wrap the whole string from the outside in, as i use a drop down menu in Excel to pass this into my query, not all the possible entries use the single quotes – PeterH Dec 07 '17 at 10:55
  • Use special characters instead of single quotes and replace it in excel parameter. – SPnL Dec 07 '17 at 11:00

2 Answers2

4

in SQL, if you want to have Single Quotes inside a string, then you should specify it as 2 consecutive single quotes for every single quote in your string. So

Where 
    pm.Alias = 'Toys'R'Us France'

should be written as

Where 
    pm.Alias = 'Toys''R''Us France'
Jayasurya Satheesh
  • 7,438
  • 3
  • 19
  • 36
  • i need to wrap the whole string from the outside in, as i use a drop down menu in Excel to pass this into my query, not all the possible entries use the single quotes – PeterH Dec 07 '17 at 10:56
  • then before passing it to the SQL query, replace each single quote with 2 singles quotes from your application code. – Jayasurya Satheesh Dec 07 '17 at 10:58
  • 2
    Your query should deal with this once passed in from Excel, or your Excel could deal with it before passing to the query: it should replace a single quote with two single quotes. – Rich Dec 07 '17 at 10:58
  • for example my other option could be 'UPIM Italy' – PeterH Dec 07 '17 at 10:59
  • if you want the single quotes to be handled by the SQL, then, assign the value to a variable and in the where condition, check against the variable - then you don't have to replace the quotes. else you have to handle it in the excel or application code – Jayasurya Satheesh Dec 07 '17 at 11:02
  • how would i do that, i tried Declare (at)store varchar(15); Set (at)store = 'Toys'R'Us France' note cannot use propper at sign in comment – PeterH Dec 07 '17 at 11:05
  • it's better go for handling it from the application code since it more easy and safe. – Jayasurya Satheesh Dec 07 '17 at 11:10
0

You might try using extra quotes after and before the existing quotes. In this case add quote before and after 'R', and the query will be like below.

Where 
    pm.Alias = 'Toys''R''Us France'
Shammas
  • 291
  • 1
  • 4
  • 15