Bit wise in sql server

  • I have about 5 columns with 0,1,-1,-2

    I want recode these values to 0,1,space, space

    I am thinking the bit wise operation but unable to find a mask that recode 0 to 0, 1 to 1, -1 to space and -2 to space. Any Help.

    Txs

    Alex,

  • alex2000 (12/15/2009)


    I have about 5 columns with 0,1,-1,-2

    I want recode these values to 0,1,space, space

    I am thinking the bit wise operation but unable to find a mask that recode 0 to 0, 1 to 1, -1 to space and -2 to space. Any Help.

    Txs

    Alex,

    Whant you exactly want to do?

    When selecting data from the column then substitute -1 and -2 with a space?

    in this case you can simply use a CASE

    SELECT

    CASE WHEN Col = -1 OR Col = -2 THEN ' '

    ELSE Col

    END AS Col

    FROM aTable

    It is what you intended?

    But also note, that ifthe original values are numeric values, then you will need to cast them to char or varchar.

  • The table has about 20 million records and the case statement is slow. I just want to improve the performance using the bitwise operation.

    Alex

    Txs

  • It is just updating the table (5 columnX20,000,000 records= 100,000,000). and the performance of the case statement is a little bit slow. I was thinking bitwise operation might be faster.

    Txs,

    Alex

  • Update YourTable

    SET YourTable.Col1 = ' '

    WHERE YourTable.Col1 < 0

    But there are (at least) two remaining question:

    If you care about performance, why do you want to use a character (space) within a column that only contains tinyint values? Wouldn't it make more sense to either set it to NULL or to a numeric value?



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • The original table values are int, but it should be converted to char. This is the way I did it. but the performance is slow.

    SELECT

    CASE WHEN Col in (-1,-2) THEN ' '

    ELSE convert(Col, char(1))

    END,

    FROM aTable

    Thank you for the help,

    Alex

  • I was trying to use the bit wise like

    Col & mask

    and -2 & mask= ' '

    -1 & mask= ' '

    -2 & mask= ' '

    SELECT

    CASE WHEN Col in (-1,-2) THEN ' '

    ELSE convert(Col, char(1))

    END,

    FROM aTable

    Thank you for the help,

    Alex

  • I was intersted in using bitwise operation because

    Col & mask, and trying to find the "mask" value to get the following result

    -2 & mask= ' '

    -1 & mask= ' '

    0 & mask= 0

    1 & mask= 1

    Txs,

    Alex

  • Even though I still don't know why an integer column needs to be converted into a character data type... Here's how I'd do it if I'd be forced to:

    Step 0:

    Verify, that the column is not used in any foreign key relationship.

    Step 1:

    Disable any index related to this column (to avoid time consuming index updates)

    Step 2:

    Change the negative values to an unused single digit number:

    Update YourTable

    SET YourTable.Col1 = 3

    WHERE YourTable.Col1 < 0

    Step 3:

    Change the data type

    ALTER TABLE YourTable ALTER COLUMN Col1 char(1)

    Step 4:

    Change the intermediate value to space character

    Update YourTable

    SET YourTable.Col1 = ' '

    WHERE YourTable.Col1 = 3

    Step 5:

    Rebuild indexes as required



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

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

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