Finding Numbers in between two numbers - Microsoft SQL / MS SQL Server

Hi I want to find a number in between tow constant numbers in a single query and it should return as rows. (note : i dont want to resut from a table) for eg.: if i am having number 1 and 10. i should get the numbers inbetween 1 to 10 as rows thanks...

1. Finding Numbers in between two numbers

Hi
I want to find a number in between tow constant numbers in a single query
and it should return as rows.
(note : i dont want to resut from a table)

for eg.: if i am having number 1 and 10. i should get the numbers inbetween
1
to 10 as rows
thanks

Balaji Guest

2. Re: Finding Numbers in between two numbers

Is there any built-in functions for getting the numbers??

"raydan" <nospamcom> wrote in message
news:phx.gbl... [/ref]
query
> inbetween
>
>[/ref]

Balaji Guest

3. Re: Finding Numbers in between two numbers

Try followng query (Courtesy: Anith Sen
http://www.bizdatasolutions.com/tsql/tblnumbers.asp):

(to generate serial numbers between 1 and 1000)

SELECT Hundred * 100 + Ten * 10 + Unit + 1
FROM (
SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL
SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL
SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL
SELECT 9) Units (Unit)
CROSS JOIN
(
SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL
SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL
SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL
SELECT 9) Tens(Ten)
CROSS JOIN
(
SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL
SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL
SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL
SELECT 9) Hundreds(Hundred)
order by 1

--
-Vishal

"Balaji" <com> wrote in message
news:phx.gbl... [/ref]
< [/ref]
> query
> > inbetween
> >
> >[/ref]
>
>[/ref]

Vishal Guest

4. Re: Finding Numbers in between two numbers

Not really, but try this:

set nocount on
declare x int
set x = 2

create table #table (x int primary key)

while x < 10
begin
insert into #table values (x)
set x = x + 1
end

select * from #table

drop table #table

"Balaji" <com> wrote in message
news:phx.gbl... [/ref]
< [/ref]
> query
> > inbetween
> >
> >[/ref]
>
>[/ref]

raydan Guest

Posting Permissions

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