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)

>>>