Skip to main content
VTHive
VTHive
Bronze ⭐️⭐️⭐️
May 1, 2024
Question

how do I get all the variable names in a string before an operator in Hive

Hi I have a table where I have multiple conditions using operators or conditions. 

so, for each of the rows I need to get the variable name which has _

 

CONS_GRP = 'CCA'
HL_GROUP_IN in ('1_HNO', '2_HO_W') and trim(PROD_SEG_MODL_DE) = 'VASO'

CONS_SVC_GRP = 'CCA' and olb_cust ne 'A'

output:

for row1:   Cons_grp

for row2: HL_GROUP_IN 

PROD_SEG_MODL_DE

 

for row3: CONS_SVC_GRP 

              olb_cust 

 

I am okay if the output is in a row or column. appreciate any help.

 

 

    1 reply

    RAGHUY
    Gold ⭐️
    RAGHUY
    Gold ⭐️
    May 2, 2024

    @VTHive 

    Assuming you have a table named your_table with a column named condition,  you can extract the variable names using SQL:

    SELECT
    SUBSTRING_INDEX(SUBSTRING_INDEX(condition, '=', 1), ' ', -1) AS variable_name
    FROM
    your_table
    UNION
    SELECT
    SUBSTRING_INDEX(SUBSTRING_INDEX(condition, ' in ', 1), ' ', -1) AS variable_name
    FROM
    your_table
    WHERE
    condition LIKE '% in %'
    UNION
    SELECT
    TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(condition, '=', 1), ' ', -1)) AS variable_name
    FROM
    your_table
    WHERE
    condition LIKE '% ne %';


    The query will extract the variable names from the conditions in the condition column of your table. It handles conditions with =, in, and ne operators. Adjust the table and column names accordingly to fit your actual schema

    Translate
      Terms & ConditionsAccessibility statement