SQL - parse char string using external definition

Many times, in existing customers ERPs, there are fields where structured information is stored in a single big CHAR string. Usually the intent is to have a generic data carrying field in the same table, where the type of information is stored in another field, to guide parsing. A similar way to store information is seen in open systems where a generic field containing a json string variable is stored in a string field at the record level. It is a similar thing, but here with fixed plain data.

Reading the information with RPG is not a problem and efficient (just move the field in the proper DS based on the type of information) and easy for this type of thing.

Not so much with SQL (say when one needs to extract data, ETL systems, queries etc.).

So, in practice, many times I see people using a view using a lot of SUBSTRING and casting to parse the row, duplicating the effort greatly, errors etc.

So, please introduce function or a table function, like for example an hypothetic PARSE_USING(string, externalds)

where string is the string to parse

externalds the external data structure to use

A function/table function would suffice, any other functionality is provided by composition capability of the SQL engine (i.e. CROSS LATERAL JOINS subqueries etc.).

--

SELECT b.field1, b.field3

FROM TABLE( PARSE_USING('CY W9000001NYVZV01900 W05', 'TABWDS' )) b

--

where field1 and field2 are defined in the external file object TABWDS.

thanks