Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2005
    Posts
    71

    Unanswered: 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.

  2. #2
    Join Date
    Mar 2005
    Posts
    71

    ok make that a char(9) field that is right justified...

    ' 200'
    ' 21'
    ' 2005'
    sorry for confusion just been a long hard day...

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Code:
    DECLARE @MyChar9 CHAR(9)
    SET @MyChar9 ='        20'
    SELECT @MyChar9,LEFT(LTRIM(@MyChar9),1)
    WHERE LTRIM(@MyChar9) LIKE '2%'
    
    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Correct me if I'm wrong but...
    Using LTRIM and LIKE will ignore any indexes.
    George
    Home | Blog

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    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
    Code:
    ....
    @MyChar9 LIKE ' 2%'
    No indexes used there either but less RSI from all that typing.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    .... unless of course there might not be leading spaces in which case ignore.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Nov 2002
    Posts
    272
    Quote Originally Posted by pootle flump
    .... 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().

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by ivon
    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).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    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:
    Code:
    where column like 'abcd%'
    But this will like totally gag your server:
    Code:
    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 ;-)

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by hicpics
    ' 200'
    ' 21'
    ' 2005'
    sorry for confusion just been a long hard day...

    How is a char(9) field right justified?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •