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]

Sybase ASE provides the SUBSTRING() function, to extract a substring from a string expression:

SELECT .... FROM tab1 WHERE  SUBSTRING(col1,2,2)  = 'RO'
SELECT SUBSTRING('Some text',6,3 ) FROM DUAL -- Gives 'tex'

Solution

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

Note:

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

is converted to:

UPDATE tab1 SET SUBSTRING(col1 from 2 for (3-2+1)) = 'RO' WHERE ...
Column subscripts in ORDER BY expressions are also converted and produce an error with PostgreSQL:
SELECT ... FROM tab1 ORDER BY col1[1,3]

is converted to:

SELECT ... FROM tab1 ORDER BY SUBSTRING(col1 from 1 for(3-1+1))