Skip to Main Content
IBM Power Ideas Portal

This portal is to open public enhancement requests against IBM Power Systems products, including IBM i. To view all of your ideas submitted to IBM, create and manage groups of Ideas, or create an idea explicitly set to be either visible by all (public) or visible only to you and IBM (private), use the IBM Unified Ideas Portal (

Shape the future of IBM!

We invite you to shape the future of IBM, including product roadmaps, by submitting ideas that matter to you the most. Here's how it works:

Search existing ideas

Start by searching and reviewing ideas and requests to enhance a product or service. Take a look at ideas others have posted, and add a comment, vote, or subscribe to updates on them if they matter to you. If you can't find what you are looking for,

Post your ideas
  1. Post an idea.

  2. Get feedback from the IBM team and other customers to refine your idea.

  3. Follow the idea through the IBM Ideas process.

Specific links you will want to bookmark for future use

Welcome to the IBM Ideas Portal ( - Use this site to find out additional information and details about the IBM Ideas process and statuses.

IBM Unified Ideas Portal ( - Use this site to view all of your ideas, create new ideas for any IBM product, or search for ideas across all of IBM. - Use this email to suggest enhancements to the Ideas process or request help from IBM for submitting your Ideas.

Status Not under consideration
Workspace IBM i
Categories Db2 for i
Created by Guest
Created on Oct 23, 2023

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



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


Idea priority High
  • Guest
    Nov 15, 2023
    IBM does not intend to provide a solution to this Idea at this time, so it is being closed.

    While we understand the request and see its value to application development, we do not currently plan to implement it. The database team has a long list of higher priority requirements, so this request is not likely to be implemented anytime soon.

    If you are dealing with a structure that aligns with a file definition, QSYS2.SYSCOLUMNS2 was just enhanced to return buffer position information. This might be useful, in conjunction with the substring and Interpret functions, to extract the data.

    Db2 for i development team
    IBM Power Systems Development