Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

select quarie between houseno .......... Expand / Collapse
Author
Message
Posted Sunday, December 15, 2013 3:18 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, April 6, 2014 12:03 AM
Points: 30, Visits: 119
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...........
Post #1523014
Posted Sunday, December 15, 2013 5:16 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:36 AM
Points: 3,076, Visits: 3,193
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,
SQL Server developer at Seavus
www.seavus.com
Post #1523019
Posted Sunday, December 15, 2013 5:43 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, April 6, 2014 12:03 AM
Points: 30, Visits: 119
Thanks for Replay...........

but shows error

" Conversion failed when converting the varchar value '300,' to data type int. "
Post #1523021
Posted Sunday, December 15, 2013 7:29 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:36 AM
Points: 3,076, Visits: 3,193
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,
SQL Server developer at Seavus
www.seavus.com
Post #1523025
Posted Sunday, December 15, 2013 7:46 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, April 6, 2014 12:03 AM
Points: 30, Visits: 119
again it shows error like


Msg 537, Level 16, State 5, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.
Post #1523029
Posted Sunday, December 15, 2013 7:54 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:36 AM
Points: 3,076, Visits: 3,193
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,
SQL Server developer at Seavus
www.seavus.com
Post #1523032
Posted Sunday, December 15, 2013 9:32 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:36 AM
Points: 3,076, Visits: 3,193
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,
SQL Server developer at Seavus
www.seavus.com
Post #1523048
Posted Sunday, December 15, 2013 11:05 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 10:48 AM
Points: 1,037, Visits: 6,956
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?



Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Post #1523051
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse