CODEWITHSUNDEEP
mysqlsubstring
Josef
Josef

Reputation: 2726

Extract string from value and insert into field

This is mytable:

+----+-------------+  
| id | data        |  
+----+-------------+  
|  1 | DA-1111 A   |  
|  2 | DA-5334 B   |  
|  3 | DA-4532 A   |  
|  4 | DA-34 K     |  
+----+-------------+

With query:

select substring_index(substring_index(myfield, '-', -1), ' ', 1) as colB from mytable

I get extraced values between DA- and last letter. Now I want extract value from field and in the same time insert that value into new field in the same table. The final result should be:

+----+-------------+------+  
| id | data        | colB |
+----+-------------+------+  
|  1 | DA-1111 A   | 1111 |  
|  2 | DA-5334 B   | 5334 | 
|  3 | DA-4532 A   | 4532 | 
|  4 | DA-34 K     | 34   | 
+----+-------------+------+

Is that possible and how to do that?

Upvotes: 1

Views: 46

Answers (1)

Renzo
Renzo

Reputation: 27424

You could do:

update mytable
set colB = substring_index(substring_index(myfield, '-', -1), ' ', 1)

Of course the table should already have a field named colB, otherwise you can create it with an ALTER TABLE statement.

Upvotes: 1

Related Questions