Substrings in SQL
Informix®
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]Important: With other database servers as Informix, when the subscript notation is used to modify column values in UPDATE statement, or
as ORDER BY element, you will get and SQL error:
UPDATE tab1 SET col1[2,3] = 'RO' WHERE ...
SELECT ... FROM tab1 ORDER BY col1[1,3]IBM® DB2®
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
Replace all Informix col[x,y]
right-value expressions by SUBSTRING(col,x,y-x+1,CODEUNITS32).
Rewrite UPDATE and ORDER BY clauses using
col[x,y] expressions.
The translation of
col[x,y] expressions can be controlled with the following FGLPROFILE
entry:dbi.database.dsname.ifxemul.colsubs = { true | false }