• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 313
  • Last Modified:

Update Single Quote with Double Quote in SQL Server 2005

Hello Experts,

I have string like  john'King, whenever I tried to Insert/Update my query fails. Due to single quote in the string, I need to replace all my single quote with double.


Thanks,
ASPDEV
0
ASPDEV
Asked:
ASPDEV
  • 5
  • 4
  • 4
  • +1
1 Solution
 
dwe761Software EngineerCommented:
Something like this:

update [YourTable]
set [YourField]= replace([YourField], '','''')
where patindex('',[YourField]) <>0
0
 
dwe761Software EngineerCommented:
Actually, I had better luck with CharIndex on this one and you need 4 single quotes rather than 2:

update [YourTable]
set [YourField]= replace([YourField], '','''')
where charindex('''',[YourField]) <>0
0
 
ASPDEVAuthor Commented:
I tried both , it didn't work
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
HainKurtSr. System AnalystCommented:
what do you have in table and what column are you trying to update?
0
 
dwe761Software EngineerCommented:
So sorry, just trying to go too fast:
Use this instead.

The REPLACE command must have 4 single quotes and then 6 single quotes
The WHERE clause must have 4 single quotes:

update [YourTable]
set [YourField]= REPLACE ([YourField], '''','''''')
WHERE charindex('''',[YourField]) <>0
0
 
HainKurtSr. System AnalystCommented:
post your query... if you are creating sql in your app like

sql = "update mytable set full_name = '" & full_name & "' where id = " & id

then you should modify it like

sql = "update mytable set full_name = '" & replace(full_name,"'","''") & "' where id = " & id


0
 
Christopher GordonSenior Developer AnalystCommented:
Are you using dynamic SQL to update the database?  Would you be able to post your update code?  You should be allowed to put a single quote into the database.
0
 
HainKurtSr. System AnalystCommented:
looks like insufficient info here... without related code, everybody will waste time :)
0
 
ASPDEVAuthor Commented:
Okay,

I 'm trying to update table [EMP] with field [NAME] as per above request..

TABLE-EMP

NAME          
----------
JOHN 'King
GREY 'JOEL
KIMS 'LEE


I need like this

NAME
------
JOHN ''KING
GREY "JOEL
KIMS "LEE
0
 
dwe761Software EngineerCommented:
My last query will do that.  Just replace it with your table name and your field name.
0
 
ASPDEVAuthor Commented:
dwe761:,

I tried you sql statement :

update [YourTable]
set [YourField]= REPLACE ([YourField], '''','''''')
WHERE charindex('''',[YourField]) <>0

Now it again updates all the records, which already updated with double quotes to 2X Double quotes..
0
 
HainKurtSr. System AnalystCommented:
look at this:

declare @name varchar(100)='Hain''Kurt';
select @name, REPLACE(@name,'''','''''')

Hain'Kurt      Hain''Kurt

what query are you using...

update myTable set name = replace(name,'''','''''')
where name <> replace(name,'''','''''')

should work...
0
 
ASPDEVAuthor Commented:

Okay, finally this worked

update [YourTable]
set [YourField]= REPLACE ([YourField], '''','"')
WHERE charindex('''',[YourField]) <>0
0
 
ASPDEVAuthor Commented:
Thanks.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 4
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now