Match substrings and extract strings based on conditional statements in Postgresql 11.0

I have following table in postgres 11:

level1          level2      level3          level4            A (alimentary)  A05 (bile)  A05C (drugs)    A01AA (caries)  A (alimentary)  A05 (bile)  A05C (drugs)    A01AA (caries)  A (alimentary)  A01 (bile)  A01AA (drugs)   A01AA (caries)   

I would like add another column 'code' to the existing table, and copy the value of level4 to code if level1 is a substring of level2 and level2 is a substring of level3 and level3 is a substring of level4 (row2) else copy the level3 value if level1 is a substring of level2 and level2 is a substring of level3 (row1)

level1          level2      level3          level4          code  A (alimentary)  A05 (bile)  A05C (drugs)    null            A05C  A (alimentary)  A01 (bile)  A01AA (drugs)   A01AA (caries)  A01AA   

I am using following query to execute this:

SELECT level1.*,                 level2.*,                 level3.*,                 level4.*,                 case                      when (level1 like 'level2') and (level2 like 'level3') and (level3 like 'level4') then substring(atc_code_level4, 1, 5)                      when (level1 like 'level2') and (level2 like 'level3') then substring(atc_code_level3, 1, 4)                      else level2                 end as code          FROM (SELECT * from level1) as level1,                   (SELECT * from level2) as level2,                   (SELECT * from level3) as level3,                  (SELECT * from level4) as level4  

https://stackoverflow.com/questions/65735259/match-substrings-and-extract-strings-based-on-conditional-statements-in-postgres January 15, 2021 at 07:25PM