How to query a number (street number)...

I have a table that has a street number field.
if the user types in a street number of '2' i would like to return all street numbers the begin with 2 (2,20,21, 200, 201,205,2009,...)
how can this be done.' 200'
' 21'
' 2005'
sorry for confusion just been a long hard day...|||If your street number column is a string, just use the LIKE operator.
If it is not a string, then it should be. Change it.

Rule Of Thumb: If you don't add it, subtract it, multiply it, or divide it, then it is not a number even if it looks like one.|||DECLARE @.MyChar9 CHAR(9)
SET @.MyChar9 =' 20'
SELECT @.MyChar9,LEFT(LTRIM(@.MyChar9),1)
WHERE LTRIM(@.MyChar9) LIKE '2%'


GW|||Correct me if I'm wrong but...
Using LTRIM and LIKE will ignore any indexes.|||Correct me if I'm wrong but...
Using LTRIM and LIKE will ignore any indexes.I cannot because you are not :)

@.OP - simpler just to write
...
@.MyChar9 LIKE ' 2%'
No indexes used there either but less RSI from all that typing.|||... unless of course there might not be leading spaces in which case ignore.|||... unless of course there might not be leading spaces in which case ignore.
In a right justified char(9) column there may be up to 8 leading spaces. Hence Gwilly's LTRIM().|||In a right justified char(9) column there may be up to 8 leading spaces. Hence Gwilly's LTRIM().But there might be none which was why I said ignore my solution (which is likely wrong).|||LIKE will like totally use the index, if the wildcard comes at like the end or whatever. So like this will do an index seek:

where column like 'abcd%'

But this will like totally gag your server:

where column like '%wxyz'

It's like when you wanna text your friend, and you like only know their last name or whatever, you have to go through each one, since the index is ordered by like the first name, you know.

OK. I will go back to English, now ;-)|||' 200'
' 21'
' 2005'
sorry for confusion just been a long hard day...

How is a char(9) field right justified?