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

  • Question

  • 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

  • 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

  • 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
  • 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
  • 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