How to insert single quote string into a table.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to insert single quote string into a table.

Author

Topic

ganny
Yak Posting Veteran

51 Posts

Posted - 2010-08-26 : 05:52:38

Hi,

I need to update the single quoted string values into my table. I tried as below but getting error message. please help me, how to insert the string with single quote without replacing it.

Query:
INSERT INTO access_control VALUES ('"+employee+"', '"+department+"', '"+desgination+"')";

employee name contains single quotes, for example:
1. texa's
2. feather's
3. d'souza

so the above query is not allowing to insert the employee name into database. I also tried the same as below as well:

INSERT INTO access_control VALUES (replace('"+employee+"','''',''''), '"+department+"', '"+desgination+"')";

But, this one is also not working.

Kindly assit me.

Regards/Ganny.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-08-26 : 06:30:44

Are you selecting value from any other table or using variables for inserting data. I don't see any @ symbol before name nor any select statment.

You need to use two single quotes and not double quote.

Example:

declare @Sample table
(
employee varchar(255),
department varchar(255),
desgination varchar(255)
)

Insert into @Sample Values ('test''value','accounts','Manager')

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-08-26 : 06:36:35

Read much about quotes here

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/02/19/understanding-single-quotes.aspx

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

ganny
Yak Posting Veteran

51 Posts

Posted - 2010-08-26 : 06:41:17

Hi,

I am using variable for inserting data. The employee name will get it from web page. I am passing the name to a variable called "employee".

so, please advice me how to replace the single quote to double quote of the content. Since we can not put extra quote since its variable.

Regards/Ganny.

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-08-26 : 06:51:03

Try this

select replace(@employee,'''','''''')

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-08-26 : 07:43:01

Are you using dynamic sql?

If there is a single quote inside a variable then you don't need to escape it. You can just use the variable.

Maybe you should post the relevant code you have now?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources