Forum Discussion

FabricUser_5037's avatar
FabricUser_5037
Frequent Visitor
6 months ago
Solved

Bit values conversion

I have a column with BIT datatype in a table in warehouse.

I want the values to be converted into TRUE and FALSE in a query, without changing it's datatype. Also, I don't want to use CASE Statements. Is there anyway by which i can achieve this?
I have tried casting the column into bool/boolean, but i get an error stating 
"Type BOOL is not a defined system type"

 

One Lake
  • svenchio's avatar
    svenchio
    6 months ago

    Hey FabricUser_5037   ... sharing 4 options  😉 without case, I would go for option 2 if your columns is nullable, it's simple and compact 😁🤞 

     

    create table demo2 (
value_col BIT NOT NULL 
); 
insert into demo2 (value_col) values (0); 
insert into demo2 (value_col) values (1); 
select * from demo2

--Option 1: IIF 
SELECT
    IIF(value_col = 1, 'TRUE', 'FALSE') AS MyBitAsText
FROM dbo.demo2;

--Option 2: CONVERT + NULLIF if you have nullable BIT
SELECT
    COALESCE(IIF(value_col = 1, 'TRUE', 'FALSE'), NULL) AS MyBitAsText
FROM dbo.demo2;

--Option 3: CHOOSE 
SELECT
    CHOOSE(CAST(value_col AS int) + 1, 'FALSE', 'TRUE') AS MyBitAsText
FROM dbo.demo2;

--Option 4: Double REPLACE on a cast
SELECT
    REPLACE(REPLACE(CAST(value_col AS varchar(1)), '1', 'TRUE'), '0', 'FALSE') AS MyBitAsText
FROM dbo.demo2;

     

    Hope this solve your request, if so, please accept this as solution and give us a kudos if you find this useful. 

    All the very best! Cheers. 

     

3 Replies

  • svenchio's avatar
    svenchio
    Super User
    6 months ago

    Hey FabricUser_5037   ... sharing 4 options  😉 without case, I would go for option 2 if your columns is nullable, it's simple and compact 😁🤞 

     

    create table demo2 (
value_col BIT NOT NULL 
); 
insert into demo2 (value_col) values (0); 
insert into demo2 (value_col) values (1); 
select * from demo2

--Option 1: IIF 
SELECT
    IIF(value_col = 1, 'TRUE', 'FALSE') AS MyBitAsText
FROM dbo.demo2;

--Option 2: CONVERT + NULLIF if you have nullable BIT
SELECT
    COALESCE(IIF(value_col = 1, 'TRUE', 'FALSE'), NULL) AS MyBitAsText
FROM dbo.demo2;

--Option 3: CHOOSE 
SELECT
    CHOOSE(CAST(value_col AS int) + 1, 'FALSE', 'TRUE') AS MyBitAsText
FROM dbo.demo2;

--Option 4: Double REPLACE on a cast
SELECT
    REPLACE(REPLACE(CAST(value_col AS varchar(1)), '1', 'TRUE'), '0', 'FALSE') AS MyBitAsText
FROM dbo.demo2;

     

    Hope this solve your request, if so, please accept this as solution and give us a kudos if you find this useful. 

    All the very best! Cheers. 

     

  • v-sshirivolu's avatar
    v-sshirivolu
    Community Support
    5 months ago

    Hi FabricUser_5037 ,

    I would also take a moment to thank svenchio  , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
     

    I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions

     

    • v-sshirivolu's avatar
      v-sshirivolu
      Community Support
      5 months ago

      Hi FabricUser_5037 ,

      I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need.