CODEWITHSUNDEEP
sql-serversql-server-2005
user3073987
user3073987

Reputation: 53

Update part of the date in SQL server

i have table like this 

ID Value
112 x
113 y
114 z
115 e

and i want to update ID cell with 'x' in the end so output updated value should be '112x' how to do that?

Upvotes: 0

Views: 83

Answers (4)

t-clausen.dk
t-clausen.dk

Reputation: 44326

Currently the other answers suggest

UPDATE Table SET ID = CAST(ID AS VARCHAR(20)) + Value
FROM <table>

This is NOT a good answer unless ID is of the type char(x). Most likely the ID column is an integer, which will cause a conversion error.

If ID is already a varchar(x), no casting is necessary, all you have to do is:

UPDATE Table SET ID = ID + Value
FROM <table>

Assuming ID is an integer. You could alter the column, so it can contain a varchar. This could cause problems if ID is autoincremential and everywhere else you are currently using this column. Using varchar as an ID column is not good practice eather. I don't recommand this.

But why would you combine an ID with a varchar value ? Those values should be in different columns. Instead you could do 1 of these 4 suggestions

-create a view containing the combination

CREATE VIEW v_test as 
SELECT ID, Value, CAST(ID AS VARCHAR(10)) + Value as CID FROM <table>

-create a computed column containing the combination

ALTER table <table> ADD cID as CAST(ID AS VARCHAR(10)) + Value

-create a computed column containing the combination with a possible index. Persisted computed columns allows index.

ALTER table <table> ADD cID as CAST(ID AS VARCHAR(10)) + Value PERSISTED

-display the value in a select (which is what I assume you need)

SELECT CAST(ID AS VARCHAR(10)) + Value as DisplayID
FROM table

Upvotes: 0

Premchand Yelavarthi
Premchand Yelavarthi

Reputation: 274

If ID column having the datatype INT, its not possible to update with Varchar datatype.

UPDATE Table SET ID = CAST(ID AS VARCHAR(20)) + Value

Won't works.

My suggession is

Create new column with varchar datatype then apply

Alter table Tab add Nwcol varchar(100)

UPDATE Table SET NewCol = CAST(ID AS VARCHAR(20)) + Value

Then rename the col with old name

sp_rename 'table.new_column','old_column','Column'

Its done.

Upvotes: 0

Nayeem Mansoori
Nayeem Mansoori

Reputation: 831

Have you try this :

 Update tablename set ID = CAST(ID AS VARCHAR(20)) + Value

Upvotes: 1

Upendra Chaudhari
Upendra Chaudhari

Reputation: 6543

For that you need ID column of type VARCHAR data type. So you can update it by

UPDATE Table SET ID = CAST(ID AS VARCHAR(20)) + Value

Otherwise you can add new column with VARCHAR data type and update in that like below 

UPDATE Table SET NewCol = CAST(ID AS VARCHAR(20)) + Value

Upvotes: 2

Related Questions