locked
Best way to go from varchar to bit data type RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    I need to change a few data type fields from varchar to bit. The data looks like "-1" and "0". What is the best way to about this?

     

    Thanks,

    Wednesday, December 28, 2011 3:03 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    BIT does not accept -1. It only has 1 and 0. The -1 can become 1, and the output can be converted converted if required.

    The Implicit Conversions chart (listed in CAST and CONVERT) has varchar to bit as an implicit conversion. Hence, you can UPDATE the -1 to be 1 and change the type. Here is an example:

    CREATE TABLE A(A VARCHAR(2));
INSERT INTO A SELECT 0 UNION ALL SELECT -1;
UPDATE A SET A = 1 WHERE A = -1;
ALTER TABLE A ALTER COLUMN A BIT;
DROP TABLE A;


     

    Wednesday, December 28, 2011 3:21 PM
    Answerer

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote
    I don't believe bit column accept "-1" it accepts only 1 or 0
    http://uk.linkedin.com/in/ramjaddu
    Wednesday, December 28, 2011 3:09 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    or NULL :-)
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, December 28, 2011 3:13 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    BIT does not accept -1. It only has 1 and 0. The -1 can become 1, and the output can be converted converted if required.

    The Implicit Conversions chart (listed in CAST and CONVERT) has varchar to bit as an implicit conversion. Hence, you can UPDATE the -1 to be 1 and change the type. Here is an example:

    CREATE TABLE A(A VARCHAR(2));
INSERT INTO A SELECT 0 UNION ALL SELECT -1;
UPDATE A SET A = 1 WHERE A = -1;
ALTER TABLE A ALTER COLUMN A BIT;
DROP TABLE A;


     

    Wednesday, December 28, 2011 3:21 PM
    Answerer