Worked perfectly once I also ran Sergey's test for anything else that could not be converted to numeric. Thanks Borislav!


>Tracy,
>This works fine for me. Try it in QA
>

>DECLARE @Test TABLE (Number varchar(200))
>INSERT INTO @Test VALUES ('301-1')
>INSERT INTO @Test VALUES ('302-B')
>INSERT INTO @Test VALUES ('303A')
>INSERT INTO @Test VALUES ('304AZ')
>INSERT INTO @Test VALUES ('305-AB')
>INSERT INTO @Test VALUES ('306-Z')
>SELECT CAST(LEFT(Number, PATINDEX('%[-A-Z]%', Number + 'A')-1) AS int), Number FROM @Test
>      WHERE CAST(LEFT(Number, PATINDEX('%[-A-Z]%', Number + 'A')-1) AS int) BETWEEN 302 AND 305
>

>
>
>>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.