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

VTHive — Wed, 01 May 2024 17:25:42 GMT

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.

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

RAGHUY — Thu, 02 May 2024 05:18:30 GMT

@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

question Re: how do I get all the variable names in a string before an operator in Hive in Support Questions

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

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