Creating a SQL Command 1

[szaunbr]

Hello,

I am working with an Informix DB and CR9 with an ODBC.
I have one field (prep_obj) that is a string field with 8 characters. Imbedded in those 8 characters are 4 characters that I would like to pull out.

ex. G2234628 is the raw field, and I need to pull out the 2234 (characters 2-5).

I know how to write a formula in crystal to do this using the mid function, but I would like to write a SQL Command to do this and make the server do the work.

I tried this:
select mid(prep_obj,2,4) from premppay;

but I keep getting the following error:
Failed to open a rowset.
Details: HY000:[DataDirect][ODBC Informix driver][Informix]Routine (mid) cannot be resolved.

I'm stumped. Any help would be appreciated.

 

[synapsevampire]

Try:

substr(prep_obj,2,4)

or try:

Substring(prep_obj FROM -2 FOR 4)

-k

 

[synapsevampire]

Sorry, the second example should be:

or try:

Substring(prep_obj FROM 2 FOR 4)

-k

 

[szaunbr]

thank you. the substr worked great. I knew there had to be a way, just couldn't figure out the nomenclature.