CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlsql-server-2012
fc123
fc123

Reputation: 918

allowing single quotes in tsql sqlserver

How to use single quotes in sql.

I don't want to use '' (double single quotes) as it will show double single quotes when data is displayed like

St. John''s

I want when data displays it should be

St. John's

And I know I can use replace but is there any alternative?

I also have tried QUOTENAME but it's not working

I have column named LOCATIONCITY and I want to insert St. John's and display St. John's But I don't want to insert double single quotes like 

St. John''s

Upvotes: 0

Views: 134

Answers (2)

Tanner
Tanner

Reputation: 22753

If you do this:

create table quote_test(quote_field varchar(50))

insert into quote_test (quote_field) values ('St. John''s')

The insert statement with the extra single quote acts as an escape character and it inserts a single quote in the specified column. Running the following commands:

select * from quote_test
select * from quote_test where quote_field = 'St. John''s'

Would return the following, with a single quote:

St. John's

If you try to insert with a single quote, the insert string would be broken and it would be invalid syntax.

Demo SQL Fiddle

Upvotes: 4

Satyajit
Satyajit

Reputation: 2210

You can declare a variable for your value and pass it as a parameter if you want to use in where clause.

In the result set the matching values will not be shown in double single quotes for single quotes.

Upvotes: 0

Related Questions