SQL adaptation guide For Oracle Database 9.2, 10.x, 11.x, 12x / 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 ORACLE provides the SUBSTR() function, to extract a substring from a string expression:
SELECT .... FROM tab1 WHERE SUBSTR(col1,2,2) = 'RO' SELECT SUBSTR('Some text',6,3)FROM DUAL -- Gives 'tex'
You must replace all Informix col[x,y] expressions by SUBSTR(col,x,y-x+1).
In UPDATE instructions, setting column values through subscripts will produce an error with ORACLE:
UPDATE tab1 SET col1[2,3]= 'RO' WHERE ...
is converted to:
UPDATE tab1 SET SUBSTR(col1,2,3-2+1)= 'RO' WHERE ...