Substrings in SQL

Informix^® SQL statements can use substrings 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]

Genero db 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' FROM 6 FOR 3) -- Gives 'tex'

Genero db 3.60 has implemented the col[x,y] expression but not the col[x]one.

Solution

With Genero db 3.61:

The Genero db database driver will convert SQL expressions containing Informix substring syntax for you. It is recommended, however, that you replace all Informix col[x,y] expressions with SUBSTRING(col FROM x FOR y-x+1).

Important:

In UPDATE instructions, setting column values using subscripts will produce an error with Genero db:

UPDATE tab1 SET col1[2,3] = 'RO' WHERE ...

is converted to:

UPDATE tab1 SET SUBSTRING(col1 FROM 2 FOR 3-2+1) = 'RO' WHERE ...

With Genero db 3.80:

The server supports all the Informix-style substrings and no conversion is done by the database driver.