how to see the spaces in the data when selecting with mysql command line client

advertisements

How can i see spaces in String when using select with the sql-command-line-client?

What i mean is the following. you have three lines. 1, 2 and 3 Spaces. You don't have a chance see the number of spaces.

create table foo(bar varchar(8));
insert into foo values(" "),("  "), ("   ");

select * from foo\g
+------+
| bar  |
+------+
|      |
|      |
|      |
+------+

mysql> select * from foo\G
*************************** 1. row ***************************
bar:
*************************** 2. row ***************************
bar:
*************************** 3. row ***************************
bar:
3 rows in set (0.01 sec)

The only option i came up with is:

mysql> select bar, hex(bar) from foo;
+------+----------+
| bar  | hex(bar) |
+------+----------+
|      | 20       |
|      | 2020     |
|      | 202020   |
+------+----------+
3 rows in set (0.01 sec)

Something like var_export in php would be nice.


It appears there is a string-function QUOTE (Escape the argument for use in an SQL statement) wich works really nice.

-- to add another tricky example
mysql> insert into foo values(" \" ' "), ("''");
Query OK, 1 row affected (0.06 sec)

mysql> select bar, quote(bar) from foo;
+-------+------------+
| bar   | quote(bar) |
+-------+------------+
|       | ' '        |
|       | '  '       |
|       | '   '      |
|  " '  | ' " \' '   |
| ''    | '\'\''     |
+-------+------------+
4 rows in set (0.00 sec)