MySQL BIT_LENGTH() function

BIT_LENGTH() function

MySQL BIT_LENGTH() returns the length of the given string in bits.

This function is useful in -

• It allows us to determine string length in bits rather than bytes.
• The function can help estimate storage requirements for a particular string.
• The function is often used with other bitwise operations or functions.

Syntax:

BIT_LENGTH (str1)

Argument:

Syntax Diagram:

MySQL Version: 8.0

Example: MySQL BIT_LENGTH() function

The following MySQL statement will return the length of the given string 'my text' in bits, i.e. 56.

Code:

SELECT BIT_LENGTH('my text'); 

Output:

mysql> SELECT BIT_LENGTH('my text'); 
+-----------------------+
| BIT_LENGTH('my text') |
+-----------------------+
|                    56 | 
+-----------------------+
1 row in set (0.02 sec)

Example of MySQL BIT_LENGTH() function using table

The following MySQL statement will return the length of each city mentioned in pub_city column in bits.

Code:

SELECT pub_city, 
BIT_LENGTH(pub_city)  AS 'bit length' 
FROM publisher; 

Sample table: publisher

+--------+------------------------------+-----------+-----------+----------------+--------------+------------+
| pub_id | pub_name                     | pub_city  | country   | country_office | no_of_branch | estd       |
+--------+------------------------------+-----------+-----------+----------------+--------------+------------+
| P001   | Jex Max Publication          | New York  | USA       | New York       |           15 | 1969-12-25 |
| P002   | BPP Publication              | Mumbai    | India     | New Delhi      |           10 | 1985-10-01 |
| P003   | New Harrold Publication      | Adelaide  | Australia | Sydney         |            6 | 1975-09-05 |
| P004   | Ultra Press Inc.             | London    | UK        | London         |            8 | 1948-07-10 |
| P005   | Mountain Publication         | Houstan   | USA       | Sun Diego      |           25 | 1975-01-01 |
| P006   | Summer Night Publication     | New York  | USA       | Atlanta        |           10 | 1990-12-10 |
| P007   | Pieterson Grp. of Publishers | Cambridge | UK        | London         |            6 | 1950-07-15 |
| P008   | Novel Publisher Ltd.         | New Delhi | India     | Bangalore      |           10 | 2000-01-01 |
+--------+------------------------------+-----------+-----------+----------------+--------------+------------+

Output:

mysql> SELECT pub_city, 
    -> BIT_LENGTH(pub_city)  AS 'bit length' 
    -> FROM publisher; 
+-----------+------------+
| pub_city  | bit length |
+-----------+------------+
| New York  |         64 | 
| Mumbai    |         48 | 
| Adelaide  |         64 | 
| London    |         48 | 
| Houstan   |         56 | 
| New York  |         64 | 
| Cambridge |         72 | 
| New Delhi |         72 | 
+-----------+------------+
8 rows in set (0.02 sec)

Use the BIT_LENGTH() function with string concatenation

To get the bit length of a concatenated string the following MySQL statement can be used.

Code:

SELECT BIT_LENGTH(CONCAT('Hello ', ' World')); 

Output:

BIT_LENGTH(CONCAT('Hello ', ' World'))|
--------------------------------------+
                                    96|

The concatenated string 'HelloWorld' has a length of 10 characters, so the total bit length is 10 * 8 = 80. Additionally, there are 16 bits for the 2 spaces between 'Hello' and 'World', resulting in a total bit length of 96.

Video Presentation:

All String Functions (Slides presentation)

PREV : BIN
NEXT : CHAR_LENGTH