Concatenate 2 columns after checking a condition

Question

I'm trying to concatenate these 2 columns "pv.name package_version_name" and "sf_li.sf_lma_version_number_c" im trying to join it with the if statement below.
[Package_version_name] ([Version_Number]) Example output: Chicago Update 2 (14.4.2) ill throw another example, Version Number: (13.0.10); package_version_name: Chicago; the output/new column (version release): Chicago (13.0.10). In some cases package_version_name already has both the fields concatenated example package_version_name: Brooklyn (9.5.14), so we can just output this alone in the new column (version release) : Brooklyn (9.5.14) without adding version number, as it is already there.

sf_li.sf_lma_version_number_c AS version_number,
pv.name package_version_name,
IF( CONTAINS(pv.Name , ")"), pv.Name, pv.Name + "(" + sf_li.sf_lma_version_number_c + ")"),
FROM "prod"."salesforce"."sf_lma_license_c" sf_li
    left join "prod"."salesforce"."sf_lma_package_version_c" pv
        on sf_li.sf_lma_package_version_c=pv.id

Answer 1

code to run:

SELECT 
   CASE 
     WHEN package_version_name NOT LIKE '%(%' OR package_version_name NOT LIKE '%)%'  
     THEN   CONCAT(        
                     CONCAT(cast(package_version_name as char(200)),
                            cast('(' as char (200))) 
                            , 
                     CONCAT(cast(version_number as char(200)),
                            cast(')' as char(200)))        
                  ) 
     ELSE 
        package_version_name   as final_version_field
 FROM 
     [TABLE NAME] / from {{ref('[TABLE NAME]')}}

Extra condition:

You can also add another condition i.e:

WHEN package_version_name NOT LIKE '%(%' or package_version_name not like '%)%' AND IS_VARCHAR(package_version_name) = 'True'

This will also check that you don't have brackets i.e ( and ) and the value is of type varchar. I have used IS_VARCHAR as i am using snowflake you can find the one related to your sql