Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2008
    Posts
    24

    Unanswered: Bitwise Shift Operators

    I know the bitwise shift operators (<< >>) are not present in the Sql Server.I like to implement one shift operation to get the network of the ip address.

    Here is the sample code below
    INET_NTOA(2601126410&(0xFFFFFFFF<<(24)))

    Let me know if anyone has implemented the Left shift and right shift operation .

    Thanks in advance

    Kiran

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    If you want the octets of an IP address...
    Code:
    DECLARE @ip char(15)
        SET @ip = '123.456.789.000'
    
    SELECT ParseName(@ip, 4)
         , ParseName(@ip, 3)
         , ParseName(@ip, 2)
         , ParseName(@ip, 1)
    George
    Home | Blog

  3. #3
    Join Date
    Dec 2008
    Posts
    24
    George,

    Thanks for your reply i don't need the octets i am just trying to get the network using the mask.

    Thanks fro your reply again
    Kiran

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    DECLARE @bin varbinary(10)
        SET @bin = 0x00000040
    
    SELECT @bin
    
    --Shift left
    SET @bin =  Convert(varbinary(10), Convert(int, @bin) * Power(2, 2))
    
    SELECT @bin
    Obviously if you're using rather large binary values, you may have to switch to bigint...

    P.S. to shift right you just change the multiply for a divide.
    Last edited by gvee; 12-17-08 at 11:11.
    George
    Home | Blog

  5. #5
    Join Date
    Dec 2008
    Posts
    24
    Thanks george
    Last edited by kirandarisi; 12-17-08 at 14:43.

  6. #6
    Join Date
    Dec 2008
    Posts
    24
    I like to shift it by 24 as i specified in my example above where i need to specify that and regarding i fount the function

    CREATE FUNCTION dbo.IntegerToIPAddress (@IP AS BIGINT)
    RETURNS VARCHAR(15)
    AS
    BEGIN
    DECLARE @Octet1 TINYINT
    DECLARE @Octet2 TINYINT
    DECLARE @Octet3 TINYINT
    DECLARE @Octet4 TINYINT
    DECLARE @RestOfIP BIGINT

    SET @Octet1 = @IP / 16777216
    SET @RestOfIP = @IP - (@Octet1 * CAST(16777216 AS bigint))
    SET @Octet2 = @RestOfIP / 65536
    SET @RestOfIP = @RestOfIP - (@Octet2 * 65536)
    SET @Octet3 = @RestOfIP / 256
    SET @Octet4 = @RestOfIP - (@Octet3 * 256)

    RETURN(CONVERT(VARCHAR, @Octet1) + '.' +
    CONVERT(VARCHAR, @Octet2) + '.' +
    CONVERT(VARCHAR, @Octet3) + '.' +
    CONVERT(VARCHAR, @Octet4))
    END

    Thanks for your great help

    Kiran

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    To shift by 24 you just change the power to Power(2, 24) I believe.

    I am intrigued; why you are being handed the IP as a bigint in the first place?
    George
    Home | Blog

  8. #8
    Join Date
    Dec 2008
    Posts
    24
    Thanks George

    I am getting it as bigint from the network packet ..i am converting it through convert bigint

Posting Permissions

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