Bonnie,

Of the top of my head

DECLARE @StreetNumber char(8)
SET @StreetNumber = '102A'
SELECT 
       -- Convert int back to string	
       CAST( 
                -- Extract # and convert to int
		CAST(LEFT(@StreetNumber, PATINDEX('%[^0-9]%', @StreetNumber + 'A')-1) AS int)
    AS char(8)) + 
         -- Add the rest of the original string
         SUBSTRING(@StreetNumber, PATINDEX('%[^0-9]%', @StreetNumber + 'A'), 8)

>I have a StreetNumber column that is CHAR(8). It needs to be character, because you can have alpha in a street number, ie: 102A First St. The problem is that I need to provide the capability to search on a range of street numbers.
>
>Simply using
>

>WHERE StreetNumber >= '300' and StreetNumber < '500'
>

>won't work, because of the fact that it returns numbers like 35, 42, etc.
>
>But, because of the possibility of alpha characters, I cannot use CAST or CONVERT to compare numerically. I thought the answer would be to pad with leading zeros and continue to do a string comparison ... but how does one do that with T-SQL? There is no PADL statement.
>
>Any ideas? Or do I just need to have a cuppa coffee before I start doing too much thinking first thing in the morning? <g>