I wish I could mark two threads as the solution, because the end resolution seems to be to run your check for any cases where number cannot be converted to INT and then use Boris' select based on the results...

Thank you SO MUCH Sergey and Borislav (and to all who looked at it)!


>As I said before, you can add more non-numeric characters to the pattern

>SELECT CAST(LEFT(number, PATINDEX('%[-,A-Z]%', number + 'A')-1) AS int) FROM occ_main
>

You can also run following select to find other cases when number cannot be converted
>

>SELECT NumPart FROM (
>           SELECT LEFT(number, PATINDEX('%[-A-Z]%', number + 'A')-1) AS NumPart FROM occ_main) np
>   WHERE ISNUMERIC(NumPart) = 0
>

>
>>As I posted to Chad, I don't know what I was thinking Sergey! I'll fool around with it some and see what I can get working. I think the headache I am suffering from this am is inhibiting my thinking abilities! (That or my age :o) The latest test:
>>
>>SELECT CAST(LEFT(number, PATINDEX('%[-A-Z]%', number + 'A')-1) AS int) FROM occ_main
>>
>>bombs with:
>>
>>Conversion failed when converting the varchar value (20,22,24) to data type int.
>>
>>It pulls 2054 records and then bombs.
>>