December 15, 2009 at 11:48 am
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,
December 15, 2009 at 1:35 pm
alex2000 (12/15/2009)
I have about 5 columns with 0,1,-1,-2I 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.
December 15, 2009 at 3:22 pm
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
December 18, 2009 at 6:07 am
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
December 18, 2009 at 6:16 am
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?
December 18, 2009 at 7:25 am
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
December 18, 2009 at 7:40 am
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
December 18, 2009 at 7:46 am
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
December 18, 2009 at 7:51 am
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy