5

I am trying to manually add a item into my table in SQL Server by using insert into statements, but I get an error.

Normally a string is added using a single apostrophe in front and back of the string in SQL Server, but I am adding a value which has an apostrophe in between (like can't), how to add this in the T-SQL insert into statement?

I did try 3 different methods to insert but still failed

insert into orders (6, 'microsoft surface pro', 'IDHGHRTUJ'''123456', 1, 8)
insert into orders (6, 'microsoft surface pro', 'IDHGHRTUJ'123456', 1, 8)
insert into orders (6, 'microsoft surface pro', "IDHGHRTUJ'123456", 1, 8)

I need output in this of the string with the apostrophe in iot

Improve this question
7
  • 3
    use 2 single apostrophes: 'IDHGHRTUJ''123456' Commented Jan 5, 2019 at 10:54
  • 3
    Try this: insert into orders values (6,'microsoft surface pro','IDHGHRTUJ''123456' ,1,8) Commented Jan 5, 2019 at 10:59
  • 2
    Instead of building strings, use SQL placeholders in your program. See bobby-tables.com . Commented Jan 5, 2019 at 11:03
  • 3
    It's better and safer to mention the column names in the statement: insert into orders (column1, column2, .....) values (6,'microsoft surface pro','IDHGHRTUJ''123456' ,1,8) Commented Jan 5, 2019 at 11:04
  • 2
    As forpas mentioned, double quotes should work. Anyway, you can check with DECLARE @Var AS NVARCHAR(20) = 'IDHGHRTUJ''123456' and then INSERT INTO ORDERS (column1, column2, column3, column4) VALUES (6,'microsoft surface pro',@Var ,1,8) Commented Jan 5, 2019 at 11:09

2 Answers 2

Reset to default
7

You can insert single quote in database by using double single quote while providing values as shown below:

create table orders (OrderId int, ProductName varchar(50), ProductDescription varchar(50), CatId int, GroupId int)
insert into orders values (6, 'microsoft surface pro', 'IDHGHRTUJ''123456', 1, 8)

select * from orders

Here is the output after insert

OrderId ProductName ProductDescription  CatId   GroupId
--------------------------------------------------------
6   microsoft surface pro   IDHGHRTUJ'123456    1   8

You can find the live demo here

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Here double single quote means two times single quote not the --- ".
0

Another way is to use CONCAT() within your insert statement.

 create table #orders (OrderId int, ProductName varchar(50), ProductDescription varchar(50), CatId int, GroupId int)
 insert into #orders values (6, 'microsoft surface pro', CONCAT('IDHGHRTUJ','''','123456'), 1, 8)

Used a temp table #orders for testing

select * from #orders
Improve this answer

Comments