Geek asks and answers

How to see data gaps when choosing using mysql command line client

How can I see spaces in String when using select using the sql command line client?

I mean the following. you have three lines. 1, 2 and 3 spaces. The number of spaces is missing.

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 across 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.

+5
source share
4 answers

There seems to be a string function QUOTE(Escape argument for use in an SQL statement) that works very well.

-- 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)
+5
source

, . , , ( -) , - . :

0

How to use String LENGTH()to get a similar result inphp var_export()

SELECT bar, LENGTH(bar) FROM foo;
0
source

You can replace spaces with other characters in the output, for example:

mysql> SELECT REPLACE('   ', ' ', '|');

+--------------------------+
| REPLACE('   ', ' ', '|') |
+--------------------------+
| |||                      |
+--------------------------+
1 row in set (0.00 sec)
0
source

More articles:


All Articles