Informix DB 11.70是否支持字符串拆分？需自行编写函数吗？

Short Answer #

Informix 11.70 does not have a native, out-of-the-box string splitting function—you’re not missing anything! This feature wasn’t included in this version, so you’ll need to use workarounds or build your own solution.

Workaround Options #

1. Create a Custom User-Defined Function (UDF) #

This is the most scalable approach if you need to split strings regularly. Here’s a practical example of a function that takes a string and delimiter, returning each split segment as a separate row using WITH RESUME:

CREATE FUNCTION split_string(input_str VARCHAR(255), delimiter CHAR(1))
RETURNING VARCHAR(255) WITH RESUME;
    DEFINE pos INT;
    DEFINE temp_str VARCHAR(255);
    -- Add delimiter to end to ensure we capture the final segment
    LET temp_str = input_str || delimiter;
    LET pos = LOCATE(temp_str, delimiter);
    
    WHILE pos > 0
        -- Skip empty segments if delimiter is at the start of the string
        IF pos > 1 THEN
            RETURN SUBSTRING(temp_str FROM 1 TO pos-1);
        END IF;
        -- Trim the processed segment from the temporary string
        LET temp_str = SUBSTRING(temp_str FROM pos+1);
        LET pos = LOCATE(temp_str, delimiter);
    END WHILE;
END FUNCTION;

You can use it like this to split a comma-separated list:

SELECT split_string('apple,banana,cherry,mango', ',') AS fruit FROM TABLE(set{1});

This will return four rows, each containing one fruit name.

2. Manual Splitting with Built-in Functions #

For simple cases (e.g., splitting into 2-3 segments), you can skip writing a full function and combine LOCATE() and SUBSTRING() directly. For example, splitting a comma-separated string into two parts:

SELECT
    SUBSTRING(your_column FROM 1 TO LOCATE(your_column, ',') - 1) AS first_segment,
    SUBSTRING(your_column FROM LOCATE(your_column, ',') + 1) AS second_segment
FROM your_table
WHERE LOCATE(your_column, ',') > 0;

Final Notes #

If you’re dealing with complex splitting needs (like multiple delimiters or variable segment counts), the custom UDF will save you repeated code. Keep in mind that newer Informix versions added more advanced string handling features, but 11.70 relies on these workarounds.

内容的提问来源于stack exchange，提问作者Paweł Lipiński