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

Question

Post reply

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

shashianireddy

SSC Eights!

Points: 914
More actions
December 15, 2013 at 3:18 am

#281215

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

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

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

Igor Micev SSC-Dedicated Points: 33110 More actions · Answer 1

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

shashianireddy SSC Eights! Points: 914 More actions · Answer 2

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

but shows error

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

Igor Micev SSC-Dedicated Points: 33110 More actions · Answer 3

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

shashianireddy SSC Eights! Points: 914 More actions · Answer 4

again it shows error like

Msg 537, Level 16, State 5, Line 1

Invalid length parameter passed to the LEFT or SUBSTRING function.

Igor Micev SSC-Dedicated Points: 33110 More actions · Answer 5

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

Igor Micev SSC-Dedicated Points: 33110 More actions · Answer 6

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

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 7

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]