Thanks, don't know what I was thinking before. Now when I run the code it returns the error: Conversion failed when converting the varchar value '20,22,24' to data type int.

It appears to get quite a way through the select before it bombs though.

>Tracy,
>
>Your first select is selecting the numbers from the value of @StreetNumber (3505) for each row in the table. The second query is simply selecting the numbers from the value of @StreetNumber without referencing a table at all. If you don't store anything to the variable, then it contains null. That is what you are seeing in the final two queries, the first being a row for each row in the table and the second being a row for the variable itself.
>
>To use that query on your table, you need to replace the variable with the field name:
>

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

>You don't need to create the variable or fill it at all.
>
>HTH,
>Chad
>
>>This is interesting. I know for sure that one record has the value of 3505A in the number field. However, there may be more similar values such as 305B, 101-A, etc. When I run:
>>
>>DECLARE @StreetNumber varchar(10)
>>SET @StreetNumber = '3505A'
>>SELECT CAST(LEFT(@StreetNumber, PATINDEX('%[-A-Z]%', @StreetNumber + 'A')-1) AS int) FROM occ_main
>>
>>It returns 7800 records with all records having the value of '3505'
>>
>>
>>When I execute:
>>
>>SELECT CAST(LEFT(@StreetNumber, PATINDEX('%[A-Z-]%', @StreetNumber + 'A')-1) AS int)
>>
>>It returns a single record with the value of 3505
>>
>>If I run the select without issuing the SET first, then it returns either all nulls (the first example above) or a single record with the value of null.
>>
>>I am confused as to how to make this work universally for all values in the table so that I can strip out any non-numeric characters from the field in Sql Server 2000/2005 just prior to converting it to an integer so I can select a range of number records such as 100-200, 1000-3000, etc.
>>
>>
>>>You can add other characters to the pattern

>>>SELECT CAST(LEFT(@StreetNumber, PATINDEX('%[-A-Z]%', @StreetNumber + 'A')-1) AS int)
>>>-- or
>>>SELECT CAST(LEFT(@StreetNumber, PATINDEX('%[A-Z-]%', @StreetNumber + 'A')-1) AS int)
>>>

>>>>Thanks Sergey, I will. However, what if the street number is something like:
>>>>
>>>>305-1
>>>>305-B
>>>>?
>>>>>Hi Tracy,
>>>>>Try

>>>>>DECLARE @StreetNumber varchar(10)
>>>>>SET @StreetNumber =  '3505A'
>>>>>SELECT CAST(LEFT(@StreetNumber, PATINDEX('%[A-Z]%', @StreetNumber + 'A')-1) AS int)

>>>>>