CODEWITHSUNDEEP
sqldb2
Chirasmai Ampally
Chirasmai Ampally

Reputation: 29

How to extract Column names and its values from the Database table, which are concatenated

Separate the column values which are concatenated in a Database Table (column_name1, values1, columnname2, values2, ....), parsing the string.

I have a table, e.g "tab1" and has a column name " rule_sql".

"rule_sql" column has the following values - 

COUNTRY_CODE IN ('766') COMPANY_CODE IN ('03') COLLECTION_UNIT_CODE IN ('000099')   
COUNTRY_CODE IN ('624') COMPANY_CODE IN ('02') COLLECTION_UNIT_CODE IN ('BE0001','000COM','000INT','00FSCY')

Column even have values like - 

COLLECTION_UNIT_CODE IN ('006800','000205','999999','110009','007279') COUNTRY_CODE IN ('869') COMPANY_CODE IN ('01')

Columns names to be extracted/parsed are not in one order.

Expected result is - 

COUNTRY_CODE || COMPANY_CODE || COLLECTION_UNTI_CODE

766           || 03          || 000099
624           || 02          || BE0001,000COM, 000INT, 00FSCY
869           || 01          || 006800,000205,999999,110009,007279

Upvotes: 0

Views: 70

Answers (1)

GMB
GMB

Reputation: 222432

You can use regex_replace():

select 
    regexp_replace(rule_sql, '^.*COUNTRY_CODE IN \(([^)]+)\).*$', '\1') country_code,
    regexp_replace(rule_sql, '^.*COMPANY_CODE IN \(([^)]+)\).*$', '\1') company_code,
    regexp_replace(rule_sql, '^.*COLLECTION_UNIT_CODE IN \(([^)]+)\).*$', '\1') collection_unit_code
from mytable

Demo on DB Fiddle:

with mytable as (
    select 'COUNTRY_CODE IN (''766'') COMPANY_CODE IN (''03'') COLLECTION_UNIT_CODE IN (''000099'')' rule_sql from dual
    union all select 'COUNTRY_CODE IN (''624'') COMPANY_CODE IN (''02'') COLLECTION_UNIT_CODE IN (''BE0001'',''000COM'',''000INT'',''00FSCY'')' from dual
)
select 
    regexp_replace(rule_sql, '^.*COUNTRY_CODE IN \(([^)]+)\).*$', '\1') country_code,
    regexp_replace(rule_sql, '^.*COMPANY_CODE IN \(([^)]+)\).*$', '\1') company_code,
    regexp_replace(rule_sql, '^.*COLLECTION_UNIT_CODE IN \(([^)]+)\).*$', '\1') collection_unit_code
from mytable

COUNTRY_CODE | COMPANY_CODE | COLLECTION_UNIT_CODE               
:----------- | :----------- | :----------------------------------
'766'        | '03'         | '000099'                           
'624'        | '02'         | 'BE0001','000COM','000INT','00FSCY'

Note: to remove the remaining quotes, then you wrap the results with replace():

select 
    replace(regexp_replace(rule_sql, '^.*COUNTRY_CODE IN \(([^)]+)\).*$', '\1'), '''', '') country_code,
    replace(regexp_replace(rule_sql, '^.*COMPANY_CODE IN \(([^)]+)\).*$', '\1'), '''', '') company_code,
    replace(regexp_replace(rule_sql, '^.*COLLECTION_UNIT_CODE IN \(([^)]+)\).*$', '\1'), '''', '') collection_unit_code
from mytable

Demo:

COUNTRY_CODE | COMPANY_CODE | COLLECTION_UNIT_CODE       
:----------- | :----------- | :--------------------------
766          | 03           | 000099                     
624          | 02           | BE0001,000COM,000INT,00FSCY

Upvotes: 1

Related Questions