How to get the value of BIT(…) data type columns in binary form?

Question

I have a table like this:

// numbers
+---------+------------+
|    id   |    numb    |
+---------+------------+
| int(11) |   bit(10)  |
+---------+------------+
| 1       | 0000000001 |
| 2       | 0000000101 |
| 3       | 0000000000 |
| 4       | 0000001011 |
+---------+------------+

When I fetch numb column, the result will be like this:

// Query
mysql> SELECT numb FROM numbers WHERE id = 2

// Fetching by PHP (pdo)
$result = $stm->fetch();
$numb   = $result['numb'];
echo $numb;
//=> 5

As you see, the final result is 5. While I want to get that exact value as a string, like this 0000000101. How can I do that?

@Thamizhan By using that function, the result isn't always 10 digits. It will be depends on the number. 12 => 1100, 26 => 11010. While I want to get a binary number based 10. — Shafizadeh
– Shafizadeh, Commented May 9, 2016 at 4:36

Thamilhan · Accepted Answer · 2016-05-09 04:44:13Z

3

You can use decbin() function and sprintf for pre-pending zeros

<?php

$numb = 5;
echo sprintf("%010d",decbin($numb));

Output:

0000000101

From your comments:

12 - 0000001100
26 - 0000011010

Update1:

You can assign to $numb variable like this:

$numb = sprintf("%010d",decbin($result['numb']));

edited May 9, 2016 at 4:44

answered May 9, 2016 at 4:36

Thamilhan

13.3k5 gold badges40 silver badges60 bronze badges

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

3 Comments

Shafizadeh Over a year ago

Thx .. That's correct. +1 ..! Also as a note I want to know, can I do that in the query? I mean by using something like concat() and making numb's value as string.

Thamilhan Over a year ago

@Shafizadeh So only I added sprintf. You can assign the value similar to echo. See the updated answer

Shafizadeh Over a year ago

Look, forget using sprintf() and decbin() functions. I'm trying to do that by pure-sql (not php-side). Is that possible? All I want to know is getting the value of a numerical column as string. Something like this (doesn't work): . . . CONVERT(VARCHAR(10),numb) active . . .

Thamilhan · Accepted Answer · 2016-05-09 05:03:07Z

3

This is completely using SQL as you requested:

Using CONV() and LPAD():

LPAD(CONV(id,10,2),10,0)

Here is SQLFiddle for you

edited May 9, 2016 at 5:03

answered May 9, 2016 at 4:55

Thamilhan

13.3k5 gold badges40 silver badges60 bronze badges

4 Comments

Shafizadeh Over a year ago

Wait .. your fiddle isn't correct, where is numb column? You have created it as an alias. That's a real column in my table. In other word, numb column isn't related to id column at all.

Thamilhan Over a year ago

Then add your schema or sample data here so I might help you out

Thamilhan Over a year ago

@Shafizadeh Checkout the updated link: SQLfiddle

Shafizadeh Over a year ago

Better ... But in your fiddle numb is integer. May you please change it to BIT(10)?

Collectives™ on Stack Overflow

How to get the value of BIT(…) data type columns in binary form?

2 Answers 2

3 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related