SQL new column from existing column-CodePudding

I have a table with two columns:

column1        column2
1              ID1_1
2              ID2_2
3              ID3

I want to add a third column based on the value of the second column. If the value contain an underscore, I want to split the value by underscore and use the first part. If the value contains no underscore, I just want to insert the same value:

column1        column2      column3
1              ID1_1        ID1
2              ID2_2        ID2
3              ID3          ID3

How can I do this in SQL?

CodePudding user response：

There are various options; here are a few of them:

one (col3_a) uses regular expressions and fetches the first word (that's why _ is replaced by !)
another (col3_b) uses case expression which searches for _; if it exists, take substring that precedes it. Otherwise, take the string itself
or, regular expression again - take alphanumerics at the beginning of the string (col3_c)

SQL> with test (col1, col2) as
  2    (select 1, 'ID1_1' from dual union all
  3     select 2, 'ID2_2' from dual union all
  4     select 3, 'ID3'   from dual
  5    )
  6  select col1, col2,
  7    regexp_substr(replace(col2, '_', '!'), '\w ') col3_a,
  8    --
  9    case when instr(col2, '_') > 0 then substr(col2, 1, instr(col2, '_') - 1)
 10         else col2
 11    end col3_b,
 12    --
 13    regexp_substr(col2, '^[[:alnum:]] ') col3_c
 14  from test;

      COL1 COL2  COL3_A COL3_B COL3_C
---------- ----- ------ ------ ------
         1 ID1_1 ID1    ID1    ID1
         2 ID2_2 ID2    ID2    ID2
         3 ID3   ID3    ID3    ID3

SQL>

CodePudding user response：

If available to you, the generated column feature can come in handy :

alter table _my_table 
    add column column_3 text
    generated always as split_part(column_2, '_', 1)
    stored;

erratum: changed link