Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2011
    Posts
    5

    Unanswered: Need sql query to filter alphanumeric data

    Hi All,
    I have alphanumeric data in a Table Assets. The column name is Milepost.

    Column Values are like below

    MilePost

    1.24
    1.61
    4.56
    4.78
    5.45
    6.91
    7.19
    9.7
    11.31
    12.96
    13.49
    13.7
    15.4
    15.83
    18.5
    27.11
    27.31
    30
    33.1
    34.7
    94.9
    98
    99.55
    104.08
    104.42
    104.55
    105.1
    209.1
    NULL
    343.1
    LR 1.36
    LR 1.90
    364.91
    364.9
    YARD
    LR 12.80
    LR 1.0
    LR 4.41
    368.6
    342.4
    342.57

    Now I want to select records between 1 to LR 4.41 which should return all records between 1 to LR 4.41

    My below query is not returning proper values. So i need a correct sql query.

    Select Milepost from Assets where Milepost between '1' and 'LR 4.41'


    Thanks

  2. #2
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    What do you mean by "not returning proper values?"

    Show us what it is returning, and show us what you expect it to return, and maybe we can come up with a solution for you.

    Right now we have no idea what you expect to see and how that differs from what you are seeing (although I certainly have a hunch as to what the issue is).
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  3. #3
    Join Date
    Dec 2011
    Posts
    5
    i want values to be returned in sorted order as (say for example) and values in between 1 to LR 4.41
    1
    1.2
    1.4
    1.6
    2
    2.1
    K1.2
    K1.4
    LR 1.3
    LR 1.41
    LR 4.41

    But it is returning values which are not in between 1 to LR 4.41

    Say for example

    1
    1.2
    MP 1.34
    KY 1.34
    2.5

    I dont want values MP 1.34 and KY 1.34 which comes after LR 4.41 to be returned.

    So my query is : Select Milepost from Assets where Milepost between '1' and 'LR 4.41

  4. #4
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    I don't think it is doing what you say it is doing.

    For instance, in your list of "improper" values in your last post, you say it is returning MP 1.34.

    I don't think it is. I think you just wrote that value in an attempt to make a point, but I don't think that value actually resulted from your query.

    I just created a temporary table, inserted your full list of values, and ran the query you gave, and MP 1.34 was not one of the values returned.

    So, again, show us the list of values that you expect to see, and the list of values that you are seeing, and we can give you an answer.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  5. #5
    Join Date
    Dec 2011
    Posts
    5
    Ken,
    Thank u for all ur reply. See today is sunday and i am not office so i have given some guessed values which came in my mind. I will provide u correct data on monday.

    Now can you please let me know query which returns values between mile post
    number(say 1) and alphabet.(say MP 1.34)
    See the milepost list is not sorted order in database.

    tHANKS

Posting Permissions

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