CODEWITHSUNDEEP
mysqlsql
Konstantin Schütte
Konstantin Schütte

Reputation: 1009

SQL Convert a char to boolean

I have in my table one row with a char value. When the value is NULL then a false should be outputted. If the value is not NULL then a true should be outputted.

So when I try to set user_group.tUser to 0 or 1 then I'm getting this error:

Invalid column name 'false'.

Invalid column name 'true'.

SELECT COALESCE((SELECT name
                FROM   v_company 
                WHERE  companyId = userView.companyId), ' ') AS company, 
    userView.value                                         AS companyUser, 
    userView.display                                       AS displayedUser,
    CASE 
        WHEN user_group.tUser IS NULL THEN 0
        ELSE 1
    END                                             AS userIsMemberOfGroup 
FROM   v_user userView 
    LEFT OUTER JOIN cr_user_group user_group
                    ON ( user_group.group = 'Administrators' 
                        AND user_group.tUser = userView.value ) 
ORDER  BY company ASC, 
        displayedUser ASC

Upvotes: 0

Views: 2260

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1270371

I think this is the logic you want:

SELECT COALESCE(v.name, ' ') as company, 
       u.value as companyUser, u.display as displayedUser,
       (EXISTS (SELECT 1
                FROM cr_user_group ug
                WHERE ug.group = 'Administrators' AND
                      ug.tUser = uv.value
               ) 
       ) as userIsMemberOfGroup 
FROM v_user u LEFT JOIN
     v_company c 
     ON c.companyId = v.companyId
ORDER BY company ASC, displayedUser ASC ;

In general, MySQL is very flexible about going between booleans and numbers, with 0 for false and 1 for true.

Upvotes: 1

Alessio Cantarella
Alessio Cantarella

Reputation: 5201

You can use MySQL IF function to return 'false' when name IS NULL, else 'true':

SELECT IF(name IS NULL, 'false', 'true')
FROM table;

Upvotes: 1

OysterD3
OysterD3

Reputation: 282

SELECT 
    CASE
        WHEN name IS NULL THEN 'false'
        ELSE 'true'
    END
FROM
    table1;

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521914

A simple CASE expression would work here:

SELECT
    name,
    CASE WHEN name IS NOT NULL THEN true ELSE false END AS name_out
FROM yourTable;

We could also shorten the above a bit using IF:

IF(name IS NOT NULL, true, false)

Upvotes: 0

Related Questions