Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 458
  • Last Modified:

Removing an apostrophe using update comand

I have some data that needs to be updated. I need to update codes that have an apostrophe, removing the apostrophe. Only problem is, SQL uses Apostrophies so my script doesnt work.

Update "Patient" set "Primary Insured Gaurantor" = 'DALES0000' where "Primary Insured Gaurantor" = "D'ALES0000" <- used quotes here just so you could see exactly what I was trying to update. I am trying to remove the apostrophe from D'ALES0000. Several others to do in several tables.
0
wdabbs
Asked:
wdabbs
1 Solution
 
chapmandewCommented:
update patient
set [primary insured grantor] = replace([primary insured grantor], '''', '')
0
 
hieloCommented:
you need to use back-to-back apostrophes:
Update "Patient" set "Primary Insured Gaurantor" = 'DALES0000' where "Primary Insured Gaurantor" = 'D''ALES0000'
0
 
obtek123Commented:
set quoted_identified will work, here is the example from BOL. Once you set the quoted Identifier the single quote will be treated as data.

Good luck

SET QUOTED_IDENTIFIER OFF
GO
-- An attempt to create a table with a reserved keyword as a name
-- should fail.
CREATE TABLE "select" ("identity" INT IDENTITY NOT NULL, "order" INT NOT NULL)
GO

SET QUOTED_IDENTIFIER ON
GO

-- Will succeed.
CREATE TABLE "select" ("identity" INT IDENTITY NOT NULL, "order" INT NOT NULL)
GO

SELECT "identity","order"
FROM "select"
ORDER BY "order"
GO

DROP TABLE "SELECT"
GO

SET QUOTED_IDENTIFIER OFF
GO
0
 
wdabbsAuthor Commented:
Worked perfectly! 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.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now