Substrings in SQL

Informix^® SQL statements can use subscripts on columns defined with the character data type:

SELECT ... FROM tab1 WHERE col1[2,3] = 'RO'
SELECT ... FROM tab1 WHERE col1[10] = 'R'   -- Same as col1[10,10]
UPDATE tab1 SET col1[2,3] = 'RO' WHERE ...
SELECT ... FROM tab1 ORDER BY col1[1,3]

IBM^® DB2^® provides different functions (SUBSTR, SUSTR2, SUBSTRING), to extract a substring from a string expression:

SELECT .... FROM tab1 WHERE SUBSTR(col1,2,2) = 'RO'
SELECT SUBSTR('Some text',6,3) ...
SELECT SUBSTRING(col,1,3,CODEUNITS32) ...

Solution

You must replace all Informix col[x,y] expressions by SUBSTRING(col,x,y-x+1,CODEUNITS32).

Important:

In UPDATE instructions, setting column values through subscripts will produce an error with IBM DB2:
UPDATE tab1 SET col1[2,3] = 'RO' WHERE ...

is converted to:

UPDATE tab1 SET SUBSTR(col1,2,3-2+1) = 'RO' WHERE ...
Column subscripts in ORDER BY expressions produce an error with IBM DB2:
SELECT ... FROM tab1 ORDER BY col1[1,3]

is converted to:

SELECT ... FROM tab1 ORDER BY SUBSTR(col1,1,3-1+1)