7

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
  • 975
  • 2
  • 14
  • 36
  • 7
    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

3 Answers3

15

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,826
  • 3
  • 22
  • 39
  • 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
2

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
  • 381
  • 1
  • 4
  • 15
-1

I recently face this issue in my sqlite database, you can resolve using like this.

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

use double quote (") instead of single quote (') after equal sign.

Gaurav Mandlik
  • 525
  • 1
  • 9
  • 42