IIRC, it all started from VAL(). :)

>Ok that was ridiculous. I guess my head has been wrapped about SQL for too long recently. Can't see the obvious anymore. Thank you so much!
>
>
>>Try

>>WHERE  VAL(Number) = 111
>>

>>>Trying to make this work on VFP tables accessed via odbc:
>>>
>>>

>>>TEXT TO mystring NOSHOW TEXTMERGE
>>>SELECT name,apt_room,occup_id,number,st_prefix,street,st_type,st_suffix,apt_room FROM occ_main
>>WHERE  CAST(LEFT(Number, PATINDEX('%[-, /\@#$%^!&*()+A-Z]%', Number + 'A')-1) AS int) = 111
>>AND street = 'GUILFORD COLLEGE' AND st_type = 'RD'
>>>ENDTEXT
>>>

>>>The exact same syntax works in SQL2000/SQL2005 but not when the backend is VFP tables accessed via odbc.