How to add new column depending on other one in SQL with regular expression-CodePudding

With this query:

    SELECT  value,
            type
    FROM "my_data"

I get a table:

value   type
17      harm_2
10      harm
17      harm
11      sharm_9
76      coins

I want to add new column is_harm with values harm/not_harm depending on column type, sports if there is word harm in that column it must be "harm" and if not "not_harm". So this table should look like this:

value   type        is_harm      
17      harm_2      harm
10      harm        harm
17      harm        harm
11      sharm_9     not_harm
76      coins       not_harm

How to do that with regular expressions? As you see there are values like sharm_9

CodePudding user response：

You can use the ^ metacharacter, which matches the beginning of the string:

SELECT value, type, 
  IF(type REGEXP '^harm', 'harm', 'not_harm') AS is_harm
FROM "my_data";

CodePudding user response：

In any flavor of SQL you could use CASE and LIKE:

SELECT *
     , CASE WHEN type LIKE 'harm%' THEN 'harm' ELSE 'not_harm' END AS is_harm
FROM ...

Regular expressions are not necessary in this case.

CodePudding user response：

If you want all the starting words with harm you can use LEFT function. Keep in mind below query will not work for results like 2_harm.

select `value`,
       `type`,
        IF(LEFT(`type`,4)='harm','harm','not_harm' ) as is_harm 
from test;

Demo: https://www.db-fiddle.com/f/7yUJcuMJPncBBnrExKbzYz/85