SQL adaptation guide For PostgreSQL 8.x.y, 9.x.y / Data manipulation |
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]
While PostgreSQL provides the SUBSTR() function, to extract a substring from a string expression:
SELECT .... FROM tab1 WHERE SUBSTRING(col1 from 2 for 2) = 'RO' SELECT SUBSTRING('Some text' from 6 for 3) ... -- Gives 'tex'
You must replace all Informix col[x,y] expressions by SUBSTRING( col from x for (y-x+1) ).
UPDATE tab1 SET col1[2,3] = 'RO' WHERE ...
is converted to:
UPDATE tab1 SET SUBSTRING(col1 from 2 for (3-2+1)) = 'RO' WHERE ...
SELECT ... FROM tab1 ORDER BY col1[1,3]
is converted to:
SELECT ... FROM tab1 ORDER BY SUBSTRING(col1 from 1 for(3-1+1))