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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
Worked perfectly! Thanks
set [primary insured grantor] = replace([primary insured grantor], '''', '')