Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

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

avatar
New Contributor

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 1

avatar
Expert Contributor

@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