Trimming Field

[gkrenton]

I'm trying to trim a field & select only the distinct records - In access the sql is:

select distinct Left([col_id]),4) as Color
from colors

However this gives me a syntax error in informix. I can't find an obvious way to keep only the first 4 digits of the field. Ultimately I'll need to take the last 4 digits in another view so if you can assist me with this I can probably figure out how to pull the last 4 digits.

Thanks
Gina

 

[PHV]

Try something like this :
SELECT DISTINCT col_id[1,4] AS Color FROM colors
The syntax is: NameOfColumn[StartPosition,EndPosition]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[gkrenton]

PH you're my savior today. Thanks a ton! I should be able to hack away without anymore posts today (or so I hope)

Gina