I have a column in a SQL table called ID (ex. 1234,12345). I want to add a "LM" to every record in that column: LM1234, LM12345, etc
Add a comment
|
4 Answers
We suppose that ID column has varchar/char/... or any other string related data type., so try this:
UPDATE [TABLE_NAME] SET [COL] = 'LM'+COL
answered Jun 15, 2016 at 22:58
Vahid Farahmandian
6,6227 gold badges48 silver badges68 bronze badges
Comments
Assuming that the id is a string, just do an update:
update t
set id = 'LM' + id;
If column is not a string, then you need to make it one first:
alter table t alter id varchar(255);
update t
set id = 'LM' + id;
Also, you could just add a computed column to do the calculation:
alter table t add lm_id as (concat('LM', column))
1 Comment
Kevin M
Thank you! Stupid mistake I was doing "LM" not 'LM'
Create a view vwID:
CREATE VIEW vwID AS
SELECT "LM" + ID AS ID, <list the rest of your columns here>
;
Comments
CONCAT() should work fine:
update [TABLE_NAME] set col=CONCAT('LM', col) where col is not NULL
1 Comment
Vincenzo Lavorini
love ti, since this solution can be used with SQLAlchemy