REPLACE Integers with Text Data

I am working with a database named “Documents” that contains 4 categories of text documents, each having its own number designation in an integer datatype column named SectionTypeId:

1 = Text

2 = Report

3 = Background

4 = Index

I would like to create a new column named “DocType” in which the integer data type for each document is replaced with a varchar data type letter (1 = T, 2 = R, 3 = B, 4 = I).I was able to easily create the new column and cast the data type from integer to varchar:

--CREATE NEW COLUMN “DocType” WITH VARCHAR DATATYPE

ALTER TABLE FullDocuments ADD DocType VARCHAR(1) NULL

Go

--UPDATE NEW COLUMN WITH CAST STRING

UPDATE FullDocuments SET DocType = CAST(SectionTypeID AS VARCHAR(1))

Go

But I have problems with the REPLACE method for replacing the numbers with letters.First I tried this based on the examples in MSDN Library:

--REPLACE NUMBERS WITH LETTERS

UPDATE Fulldocuments REPLACE (DocType,"1","T")

Which produced an error message: “Incorrect syntax near 'REPLACE'.”

Thinking that the datatype may be the problem, I tried this to convert to DT_WSTR data type prior to replace:

UPDATE Fulldocuments REPLACE ((DT_WSTR,1)DocType,"1","T")

Which produced the same error message: “Incorrect syntax near 'REPLACE'.”

I have never done a REPLACE before, so any suggestions for accomplishing this would be appreciated.

Your UPDATE statement syntax is incorrect. And the 2nd syntax where you are trying to cast to DT_WSTR is not a valid SQL syntax also. See SQL Server Books Online for the complete UPDATE statement syntax and examples. UPDATE statement consists of SET, FROM and WHERE clauses. You are missing the SET clause. And you should use single-quotes preferably for string literals otherwise you will get errors most of the time depending on your SET options. Modify your update statement to:

UPDATE Fulldocuments SET DocType = REPLACE (DocType,'1','T')

But it doesn't seem like you want to create this DocType column in the first place. You should create a separate lookup table that contains the ids and description or type. You can then join with that table based on your ID value to get the description. This is much more flexible approach that doing it your way.

|||Thanks for correcting my syntax and the advice about creating a separate lookup table.