Professional Web Applications Themes

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

    Default 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. #2

    Default 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. #3

    Default 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. #4

    Default 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

Similar Threads

  1. Finding missing numbers in sequence
    By Larry in forum PERL Beginners
    Replies: 5
    Last Post: May 20th, 09:59 PM
  2. Numbers not acting like numbers
    By D'Anne in forum PERL Miscellaneous
    Replies: 3
    Last Post: October 2nd, 09:18 AM
  3. IP numbers
    By Val in forum Windows Setup, Administration & Security
    Replies: 2
    Last Post: July 11th, 04:12 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139