mysql converting bit to char not working

Question

I was trying to feed a result of a query as a parameter for another query and all was working fine except this field that has a datatype of bit. so i tried to convert the value of the field using convert() and cast() but it seems to be not working as its returning this wierd symbol of a small rectange which hava three 0's and a 1. so can anyone tell me why this is happening and how to fix it , here is my query

select CONVERT(isMale , char(5)) from person;

and the thing is it gives me the correct answer when i dont use the convert but since am giving this result to another query as a parameter it causing me the problem.

As i was reading the MySQL documentation , they use to treat a bit as a tinyint but after 5.0.3 they changed this and its the main reason this bug is happening and i would advice everybody to go for tinyint when ever posible — Robel Alemu
– Robel Alemu, Commented Jun 28, 2016 at 13:10

Bernd Buffen · Accepted Answer · 2016-06-28 13:03:45Z

3

you can use BIN function like this:

SELECT  BIN(isMale +0) from person;

sample

MariaDB [yourschema]> SELECT  BIN(b'1001' +0) ;
+-----------------+
| BIN(b'1001' +0) |
+-----------------+
| 1001            |
+-----------------+
1 row in set (0.00 sec)

MariaDB [yourschema]>

Here some stuff from MariaDB Manual:

Description

Converts numbers between different number bases. Returns a string representation of the number N, converted from base from_base to base to_base.

Returns NULL if any argument is NULL, or if the second or third argument are not in the allowed range.

The argument N is interpreted as an integer, but may be specified as an integer or a string. The minimum base is 2 and the maximum base is 36. If to_base is a negative number, N is regarded as a signed number. Otherwise, N is treated as unsigned. CONV() works with 64-bit precision.

Some shortcuts for this function are also available: BIN(), OCT(), HEX(), UNHEX(). Also, MariaDB allows binary literal values and hexadecimal literal values.

BIN is a short form from CONV(value,from,to) where you can convert from base to base

so binary 1001 = 9 as int

here i give the value in decimal (14) and convert it from base 10 to base 2

MariaDB [yourschema]> SELECT CONV(14,10  ,2);
+-----------------+
| CONV(14,10  ,2) |
+-----------------+
| 1110            |
+-----------------+
1 row in set (0.00 sec)

so, if you want to have 0 on the left you can add a value like this

MariaDB [yourschema]> SELECT CONV(8192 + 14,10  ,2);
+------------------------+
| CONV(8192 + 14,10  ,2) |
+------------------------+
| 10000000001110         |
+------------------------+
1 row in set (0.00 sec)

and then you can get n chars from right:

MariaDB [yourschema]> SELECT RIGHT(CONV(8192 + 14,10  ,2),8);
+---------------------------------+
| RIGHT(CONV(8192 + 14,10  ,2),8) |
+---------------------------------+
| 00001110                        |
+---------------------------------+
1 row in set (0.40 sec)

MariaDB [yourschema]>

edited Jun 28, 2016 at 13:03

answered Jun 28, 2016 at 12:40

Bernd Buffen

15.1k2 gold badges27 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bernd Buffen Over a year ago

@Robel Alemu - i have add some info - hope it helps

Rick James Over a year ago

RIGHT(CONCAT('00000000', ...), 8) is a slightly less kludgy way to get leading zeros.

Drone · Accepted Answer · 2016-06-28 12:35:09Z

0

I think you want to use CAST

 select CAST(isMale as CHAR) from person;

answered Jun 28, 2016 at 12:35

Drone

1,1342 gold badges13 silver badges31 bronze badges

1 Comment

Robel Alemu Over a year ago

As i mentioned in my question the both CAST() & CONVERT () were not working

Robel Alemu · Accepted Answer · 2016-06-28 12:50:13Z

0

seeing @Bernd Buffen answer i tried using the convert with +0 and it works , eventhough i dont know why

select CONVERT(isMale +0, char(5)) from person;

answered Jun 28, 2016 at 12:50

Robel Alemu

1137 bronze badges

1 Comment

Bernd Buffen Over a year ago

they want to have any BIT in the field as 0 or 1 . try the both sample - select CONVERT(b'1' +0, char(5)) and select CONVERT(b'11' +0, char(5)). thesecond give you 3 what is decimal correct but they want a binary representation like 11.

Collectives™ on Stack Overflow

mysql converting bit to char not working

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related