How to create new column from the existing column in sql

user_14122835 0
Viewed 168

I have this column which is filled by string something like:

SX-ABC01-01-2700-W-003

Let's say this column name is type_list, and I need to create a new column called type_list_no which is I need the ABC01-01 to be in the new column

How should I write my query to get the new column with the desired string in the column in SQL?

Thank you.

mysql sql
2 Answers

You may use SUBSTRING_INDEX here:

SELECT
    col,
    SUBSTRING_INDEX(SUBSTRING_INDEX(col, '-', 3), '-', -2) AS output
FROM yourTable;

screen capture from demo below

Demo

user_18632290

Use generated column:

ALTER TABLE tablename
    ADD COLUMN type_list_no VARCHAR(255)
        AS (SUBSTRING_INDEX(SUBSTRING_INDEX(type_list, '-', 3), '-', -2));

fiddle

This column will be calculated automatically from the actual value of source column. So you do not need to check that it is consistent.

This column may be indexed. In such case you may define it as VIRTUAL - it will not be stored in a table body in such case (and so it does not need in additional table space) and will be extracted from the index or recalculated from the actual source value when needed.

user_101387340
Related