CODEWITHSUNDEEP
mysqlsql
walter
walter

Reputation: 309

Change column type and set value in a proper format for this type

I have a column NODE with integer values. How can I change column type to have changes in the following format:

Old column:  New Column:
NODE         NODE
1            N0C00001
127          NOC00127
13           NOC00013

Upvotes: 1

Views: 63

Answers (2)

Venkatesh Panabaka
Venkatesh Panabaka

Reputation: 2154

You can try like these.

SELECT CONCAT('N0C', LPAD('1',5,0));

Syntax: SELECT CONCAT('N0C', LPAD(your_column_name,5,0)) from table_name;

SQL: SELECT CONCAT('N0C', LPAD(old_columnname_NODE,5,0)) as new_columnname_NODE from table_name;

Update sql: update table_name set NODE = CONCAT('N0C', LPAD(old_columnname_NODE,5,0));

Thank you.

Upvotes: 1

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

Here how you can do,

mysql> select concat(left('N0C0000',length('N0C0000')-length('1')),'1') as r;
+---------+
| r       |
+---------+
| N0C0001 |
+---------+
1 row in set (0.00 sec)

Now changing this to update command would look like

update table_name 
set node = concat(left('N0C0000',length('N0C0000')-length(node)),node);

Here is a test case

mysql> select * from test ;
+------+
| node |
+------+
| 1    |
| 127  |
| 13   |
+------+
3 rows in set (0.00 sec)

mysql> update test set node = concat(left('N0C0000',length('N0C0000')-length(node)),node);
Query OK, 3 rows affected (0.05 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from test ;
+---------+
| node    |
+---------+
| N0C0001 |
| N0C0127 |
| N0C0013 |
+---------+
3 rows in set (0.00 sec)

Upvotes: 1

Related Questions