FORMAT and LPAD/RPAD in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of FORMAT with decimal places?

Consider the following MySQL query:

SELECT FORMAT(12345.6789, 2);

What is the output of this query?

MySQL

SELECT FORMAT(12345.6789, 2);

A '12,345.68'

B '12345.67'

C '12,345.6789'

D '12345.68'

💡 Hint

FORMAT rounds the number and adds commas as thousand separators.

FORMAT rounds the number to the specified decimal places and adds commas as thousand separators. So 12345.6789 rounded to 2 decimals is 12345.68, displayed with a comma as '12,345.68'.

❓ query_result

intermediate

What does LPAD do with a string?

Given this query:

SELECT LPAD('42', 5, '0');

What is the output?

MySQL

SELECT LPAD('42', 5, '0');

A '42'

B '42000'

C '00042'

D '000042'

💡 Hint

LPAD adds characters to the left until the string reaches the specified length.

LPAD pads the string '42' on the left with '0' characters until the total length is 5. So it adds three zeros to the left, resulting in '00042'.

❓ query_result

advanced

What is the result of RPAD with a multi-character pad string?

Analyze this query:

SELECT RPAD('abc', 7, 'xy');

What is the output?

MySQL

SELECT RPAD('abc', 7, 'xy');

A 'abcxyx'

B 'abcxyxyz'

C 'abcxy'

D 'abcxyxy'

💡 Hint

RPAD repeats the pad string as many times as needed to reach the length.

RPAD pads 'abc' on the right with 'xy' repeated until length 7 is reached. 'abc' is length 3, so 4 more characters are needed. 'xyxy' is 4 characters, so result is 'abcxyxy'.

📝 Syntax

advanced

Which query will cause a syntax error?

Identify which of these MySQL queries will cause a syntax error:

A SELECT FORMAT(9876.543, 1);

B SELECT FORMAT(1234.56 2);

C SELECT RPAD('test', 6, '-');

D SELECT LPAD('test', 6, '*');

💡 Hint

Check the syntax of the FORMAT function parameters.

Option B is missing a comma between the number and the decimal places, causing a syntax error. The others have correct syntax.

🧠 Conceptual

expert

How does FORMAT handle locale in MySQL?

Which statement about the FORMAT function's locale behavior in MySQL is true?

A FORMAT can take an optional third argument to specify locale for formatting.

B FORMAT always uses the server's default locale for decimal and thousand separators.

C FORMAT ignores locale and always uses '.' for decimal and ',' for thousands.

D FORMAT uses the client's locale settings automatically without extra parameters.

💡 Hint

Check the MySQL documentation for FORMAT function parameters.

In MySQL, FORMAT(number, decimal_places, locale) allows specifying a locale string as the third argument to control decimal and thousand separators.