Substring expressions
Handle substrings expressions with different database engines.
SELECT * FROM item WHERE item_code[1,4] = "XBFG"
This syntax is specific to Informix SQL. Other database server types provide a function that extracts substrings from a character string.
Database Server Type | Substring function | col[x,y] support |
---|---|---|
IBM Informix |
|
Yes, native SQL feature |
Microsoft™ SQL Server |
|
Emulated, see details |
Oracle® MySQL / MariadDB |
|
Emulated, see details |
Oracle Database Server |
|
Emulated, see details |
PostgreSQL |
|
Emulated, see details |
SAP HANA® |
|
Emulated, see details |
SQLite |
|
Emulated, see details |
Dameng® |
|
Emulated, see details |
Informix allows you to update some parts
of a CHAR and VARCHAR column by using the substring specification ( UPDATE tab SET col[1,2]
='ab'
). This is not possible with other databases.
Review the SQL statements using substring expressions and use the database specific substring function. Depending on the database types to be used, use SUBSTR() or SUBSTRING() as provided by the database engines.
You may also want to create your own user function with the databases engines that do not support the same syntax as most of your database engines, to have a common way to extract substrings. Note that with Microsoft SQL Server, a user function call must specify the owner as prefix. Therefore, when using SQL Server, instead of creating a SUBSTR() function, it is recommended that you create a SUBSTRING() function in other database types, since SQL Server provides SUBSTRING().