SQL Anywhere Parse 'varying' substring(s) into new columns

Question

I have a DB column that consists of one long string. This string is made up of a number of sub-strings separated by CHAR(13). My issue is the sub-strings are varying in length. My goal is to parse out each sub-string (the piece between each CHAR(13)) and put this sub-string into it's own column.

The sub-strings are 'standardized' to a point example:

Voltage (VAC)=%CHAR(13)
Rating (A)=%CHAR(13)
Input Rating (A)=%CHAR(13)

-The (%) is varying in length. And the first line does not begin with CHAR(13).

I imagine I'll have to use a similar work-around but am unsure how to implement. Why we can't execute stored procedure in select statement in oracle? is there any strong reason?

Thanks in advance.

Answer 1

For specific columns:

SELECT
    SUBSTR(REGEX_SUBSTR(col1, 'Voltage \(VAC\)=[^\r]*'), 15) AS Voltage,
    SUBSTR(REGEX_SUBSTR(col1, 'Rating \(A\)=[^\r]*'), 12) AS Rating,
    SUBSTR(REGEX_SUBSTR(col1, 'Input Rating \(A\)=[^\r]*'), 18) AS Input_Rating
FROM mytable

For generic columns:

SELECT
    REGEX_SUBSTR(REGEX_SUBSTR(col1, '[^=\r]+=[^\r]*', 1, row_num), '^[^=]*') AS Name,
    SUBSTR(REGEX_SUBSTR(REGEX_SUBSTR(col1, '[^=\r]+=[^\r]*', 1, row_num), '=.*'), 2) AS Value
FROM mytable, sa_rowgenerator(1, 100)
WHERE REGEX_SUBSTR(col1, '[^=\r]+=[^\r]*', 1, row_num) IS NOT NULL