SQL programming / SQL portability |
Handle substrings expressions with different database engines.
SELECT * FROM item WHERE item_code[1,4] = "XBFG"
However, most database servers support a function that extracts substrings from a character string:
Database Server Type | Supports col[x,y] substrings? | Provides substring function? |
---|---|---|
Genero db | Yes |
SUBSTR(expr,start,length) |
IBM DB2® UDB | No |
SUBSTR(expr,start,length) |
IBM Informix (1) | Yes |
SUBSTR(expr,start,length) |
Microsoft™ SQL Server (2) | No |
SUBSTRING(expr,start,length) |
MySQL | No |
SUBSTR(expr,start,length) |
Oracle Database Server | No |
SUBSTRING(expr,start,length) |
PostgreSQL | No |
SUBSTRING(expr FROM start FOR length ) |
Sybase ASE | No |
SUBSTRING(expr,start,length) |
SQLite | No |
SUBSTR(expr,start,length) |
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 could 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, you should create a SUBSTRING() user function instead of SUBSTR().