I need to sort on a substring of an Informix database column, but I do not see "substring" listed in the database functions; is there any way to do this at the database level?

Document ID : KB000027716
Last Modified Date : 14/02/2018
Show Technical Document Details

Yes, Informix does offer syntax to do a substring. Generically the syntax is: column_name[n1, n2] where n1 is the starting position, and n2 is the ending position of the substring.

For example:

SELECT
 "emp_table"."empno", "emp_table"."ename",
 "emp_table"."job", "emp_table"."mgr"
FROM
 "emp_table"
ORDER BY
 "emp_table"."job[4,6]" ASC

This query would sort your output based on the JOB column using positions 4,5, and 6.

You will need to go to Query / Edit to make the modification. When complete, you also need to check the "Do No Modify" box.

If you need only to display a substring of a column you can use that syntax on the Select statement, for example:

SELECT
 "emp_table"."empno", "emp_table"."ename[4,6]",
 "emp_table"."job", "emp_table"."mgr"