Re: subscripting in select statement

Austin Castro wrote: > > Hi, > I'm having a problem with the following select statement. > > select [fields] > from table > where character_field[1,3,6,12] = variable > > the character field is of length 14 and is in the following format > 0-800-123-4567. The variable is 8001234567. I want to match the records > with the > variable. I get an error saying something about subscripting. Is > there anyway to work > around this? Please help. > > austin Try this: select [fields] from table where character_field[3,5] || character_field[7,9] || character_field[11,14] = variable It will be slow. I assume the more obvious (and faster) method of editing the variable to conform to the format of the character_field is not an option? In SQL it would go: select [fields] from table where character_field = '0-' || variable[1,3] || '-' || variable[4,6] || '-' || variable[7,10] Good luck. -- Peter Lancashire Information Systems Specialist, Bayer plc Eastern Way, Bury St Edmunds, Suffolk, IP32 7AH, UK Tel: +44-1635-562258, Fax: +44-1635-562281 --- If all else fails, read the instructions and the release notes. Join Infuse, the UK Informix User Group at http://www.infuse.org.uk/ ---