-- Change NTEXT data types to NVARCHAR(MAX) type - max 4000 characters
ALTER TABLE tblClaim
ALTER COLUMN ClaimCode nvarchar(MAX) NULL

ALTER TABLE tblClaimItems
ALTER COLUMN Explanation nvarchar(MAX) NULL

ALTER TABLE tblClaimItems
ALTER COLUMN Justification nvarchar(MAX) NULL

ALTER TABLE tblClaimServices
ALTER COLUMN Explanation nvarchar(MAX) NULL

ALTER TABLE tblClaimServices
ALTER COLUMN Justification nvarchar(MAX) NULL

-- Replace not allowed characters 
UPDATE tblClaim SET ClaimCode=LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(ClaimCode, ',', ' '), '\', '\\'), '''', ''), CHAR(9), ''), CHAR(10), ''), CHAR(13), '')))
where ValidityTo is null

update tblItems set ItemName=LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(ItemName, ',', ' '), '\', '\\'), '''', ''), CHAR(9), ''), CHAR(10), ''), CHAR(13), '')))
where ValidityTo is null

update tblServices set ServName=LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(ServName, ',', ' '), '\', '\\'), '''', ''), CHAR(9), ''), CHAR(10), ''), CHAR(13), '')))
where ValidityTo is null

update tblClaimItems set Explanation=LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(Explanation, ',', ' '), '\', '\\'), '''', ''), CHAR(9), ''), CHAR(10), ''), CHAR(13), '')))
where ValidityTo is null

update tblClaimItems set Justification=LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(Justification, ',', ' '), '\', '\\'), '''', ''), CHAR(9), ''), CHAR(10), ''), CHAR(13), '')))
where ValidityTo is null

update tblClaimServices set Explanation=LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(Explanation, ',', ' '), '\', '\\'), '''', ''), CHAR(9), ''), CHAR(10), ''), CHAR(13), '')))
where ValidityTo is null

update tblClaimServices set Justification=LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(Justification, ',', ' '), '\', '\\'), '''', ''), CHAR(9), ''), CHAR(10), ''), CHAR(13), '')))
where ValidityTo is null

update tblICDCodes set ICDName=LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(ICDName, ',', ' '), '\', '\\'), '''', ''), CHAR(9), ''), CHAR(10), ''), CHAR(13), '')))
where ValidityTo is null