I want to change my column value in categoriID from numbers to text.
Is this possible?
SELECT name, CAST(categoriID AS char(10))
FROM customer
WHERE categoriID = 1 AS 'new_text'
Here is a link of a pic how i want it: https://i.stack.imgur.com/NVdXR.png
1) Simplest solution would be a simple join thus:
SELECT c.name, c.categoryID, category.name AS category_name
FROM customer c
INNER JOIN -- or LEFT JOIN if categoryID allows NULLs
(
SELECT 1, 'First category' UNION ALL
SELECT 2, 'Second category' UNION ALL
SELECT 3, 'Third category'
) category(categoryID, name) ON c.categoryID = category.categoryID
I would use this solution if list of categories is small, static and if it is needed only for this query.
2) Otherwise, I would create a new table thus
CREATE TABLE category -- or dbo.cateogory (note: you should use object's/table's schema)
(
categoryID INT NOT NULL,
CONSTRAINT PK_category_categoryID PRIMARY KEY(categoryID),
name NVARCHAR(50) NOT NULL -- you should use the propper type (varchar maybe) and max length (100 maybe)
--, CONSTRAINT IUN_category_name UNIQUE(name) -- uncomment this line if you want to have unique categories (nu duplicate values in column [name])
);
GO
plus I would create a foreign key in order to be sure that categories from [customer] table exist also in [category] table:
ALTER TABLE customer
ADD CONSTRAINT FK_customer_categoryID
FOREIGN KEY (categoryID) REFERENCES category(categoryID)
GO
INSERT category (categoryID, name)
SELECT 1, 'First category' UNION ALL
SELECT 2, 'Second category' UNION ALL
SELECT 3, 'Third category'
GO
and your query will be
SELECT c.name, c.categoryID, ctg.name AS category_name
FROM customer c
INNER JOIN ctg ON c.categoryID = ctg.categoryID -- or LEFT JOIN if c.categoryID allows NULLs
I would use solution #2.
From this possible duplicate SO on SQL Server 2008:
EXEC sp_RENAME table_name , old_name, new_name
Or you could do this:
ALTER TABLE table_name RENAME COLUMN old_name to new_name;
EDIT: Just got the question. To change the datatype of the column to a text datatype, you could execute this:
ALTER TABLE table_name MODIFY column_name CHARACTER(n);
where n is the number of characters in the string, or:
ALTER TABLE table_name MODIFY column_name VARCHAR(n)
where n is the maximum number of characters in the string.
Note that these will edit the original table. If you just want to select the column as a particular datatype, you'll need to copy the table using:
SELECT column_name(s) INTO newtable [IN externaldb] FROM table1;
Then, you can modify the column datatype as shown above, and DROP the new table if you want to. Another way to do this without a separate table would be to use CAST or CONVERT
You can use this Query to change Column datatype,
Syntax:
ALTER TABLE table_name ALTER COLUMN column_name datatype;
Solution:
ALTER TABLE customer ALTER COLUMN categoriID Varchar(50);
ALTER TABLE customer CHANGE categoriID categoriID TEXT
