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.



>Tracy,
>
>I posted a sample code so you can run tests for different street values. To run it aganst a table you don't need the first 2 lines and @StreetNumber should be replaced with the table column name.

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

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

>>>>>