create new column with conditional values from existing column in MySql workbench?

Question

i have a column in my MySql table named "details" like this:

now i want to add another column named "name" which will contain only the first word of each cell of column "details", like this:

so far i have done this:

select 
    substring(details, 1, instr(details, ' ')) 
from 
    my_table

now how to add this to a column "name"?

Answer 1

Please try this.

select 
    substring(details, 1, instr(details, ' '))  As Name
from 
    my_table

Answer 2

use position() function

DEMO

select 
    substring(details, 1, POSITION(' ' IN details)) 
from 
    my_table

OUTPUT:

value
Wellcare

ALSO locate() can be used

 select 
        substring(details, 1, LOCATE(' ', details)) 
    from 
        my_table

Answer 3

You can use SUBSTRING_INDEX to extract the first word of each value. For example:

SELECT SUBSTRING_INDEX('Wellcare Value (HMO-POS)', ' ', 1) AS name

Output:

Wellcare

To add this to your table, you have a couple of options. If you are running MySQL 5.7.6 or later, you can use a generated column as described in this question and the manual e.g.

ALTER TABLE mytable ADD COLUMN name VARCHAR(50) AS (SUBSTRING_INDEX(details, ' ', 1))

If you want to save storage at the expense of fetch time, declare the column as VIRTUAL, otherwise declare it as STORED.

You can also create a VIEW which includes this expression as a column. This is similar to a VIRTUAL generated column but will also work on versions of MySQL which don't support generated columns:

CREATE VIEW mytable_view AS
SELECT *, SUBSTRING_INDEX(details, ' ', 1) AS name
FROM mytable

The final option would be to create an actual column and then update it via a trigger, but both the preceding options are preferable.