select quarie between houseno ..........

  • i have table like below

    House_No

    4-12-1000

    4-12-55/b

    4-12-1456/b/c

    4-12-12

    4-12-1398

    4-12-23

    4-12-98

    4-12-1499

    1-4-33

    3-9-55

    2-5-89/3

    i want out put like select only in between houseno ' 4-12-1000' to '4-12-1500'

    EX-

    House_No

    4-12-1000

    4-12-1398

    4-12-1456/b/c

    4-12-1499

    plz write sql quarie...........

  • Try this code

    select house_no from YourTable

    where house_no between '4-12-1000' and'4-12-1500'

    AND ISNUMERIC(SUBSTRING(house_NO,6,4))=1

    and CONVERT(INT,SUBSTRING(house_NO,6,4)) BETWEEN 1000 AND 1500

    Regards

    IgorMi

    Igor Micev,My blog: www.igormicev.com

  • Thanks for Replay...........

    but shows error

    " Conversion failed when converting the varchar value '300,' to data type int. "

  • OK,

    What about this?

    select house_no

    from YourTable

    where house_no between '4-12-1000' and'4-12-1500'

    and SUBSTRING(house_no,6,10) not like '%[^0-9]%'

    Igor Micev,My blog: www.igormicev.com

  • again it shows error like

    Msg 537, Level 16, State 5, Line 1

    Invalid length parameter passed to the LEFT or SUBSTRING function.

  • shashianireddy (12/15/2013)


    again it shows error like

    Msg 537, Level 16, State 5, Line 1

    Invalid length parameter passed to the LEFT or SUBSTRING function.

    Now corrected, pls check it.

    Igor Micev,My blog: www.igormicev.com

  • IgorMi (12/15/2013)


    shashianireddy (12/15/2013)


    again it shows error like

    Msg 537, Level 16, State 5, Line 1

    Invalid length parameter passed to the LEFT or SUBSTRING function.

    Now corrected, pls check it.

    select house_no

    from test1

    where

    len(house_no)>=6 and

    house_no between '4-12-1000' and '4-12-1500'

    and SUBSTRING(house_no,6,10) not like '%[^0-9]%'

    Igor Micev,My blog: www.igormicev.com

  • shashianireddy (12/15/2013)


    i have table like below

    House_No

    4-12-1000

    4-12-55/b

    4-12-1456/b/c

    4-12-12

    4-12-1398

    4-12-23

    4-12-98

    4-12-1499

    1-4-33

    3-9-55

    2-5-89/3

    Each one of your five current threads

    select quarie between houseno ..........

    select house_no order

    select only up to first '-' only

    display order by like 1,2,3,4,5...............plz write quarie

    display order by houseno

    relate to the same issue. Help us and you will help yourself. Please provide a sample data set which is properly representative of your data. Your data doesn't all look like "3-9-55". If it did, any one of several solutions already posted would work just fine.

    Is "3-9-55" just a Hyderabad house number or is it three data elements combined into one?


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply