Substring expressions

Handle substrings expressions with different database engines.

Only IBM® Informix® supports substring specification with square brackets:

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.

Table 1. Substrings support by database server brand
Database Server Type	Substring function	col[x,y] support
IBM DB2® LUW	`SUBSTR(expr,start,length)`	Emulated, see details
IBM Informix	`SUBSTR(expr,start,length)`	Yes, native SQL feature
IBM Netezza	`SUBSTRING(expr,start,length)`	Emulated, see details
Microsoft™ SQL Server	`SUBSTRING(expr,start,length)`	Emulated, see details
Oracle® MySQL	`SUBSTR(expr,start,length)`	Emulated, see details
Oracle Database Server	`SUBSTRING(expr,start,length)`	Emulated, see details
PostgreSQL	`SUBSTRING(expr FROM start FOR length)`	Emulated, see details
SAP® ASE	`SUBSTRING(expr,start,length)`	Emulated, see details
SAP HANA®	`SUBSTRING(expr,start,length)`	Emulated, see details
SQLite	`SUBSTR(expr,start,length)`	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.

You can also create your own SUBSTRING() user function in all databases that do not support this function, to have a common way to extract substrings. In Microsoft SQL Server, when you create a user function, you must specify the owner as prefix when using the function. Therefore, it is recommended that you create a SUBSTRING() user function instead of SUBSTR().