Clever Geek Handbook

Mysql: get excellent TEXT value

I want to receive a unique message (datatype:) TEXT

. For this I use this simple query:

SELECT DISTINCT message FROM `online_communication`

The results I am getting are as follows:

hello.. what up?
second message..
third message.....
third message.....

Why am I getting third message.....

2 times? I've also tried:

SELECT DISTINCT BINARY message FROM `online_communication`

But, getting the same number of lines in BLOB

PS: No luck with GROUP BY

.

SQLFiddle: http://sqlfiddle.com/#!9/304e4/1

+3


source to share


3 answers


This behaves as expected because your values ​​are different. From your SQL script:

INSERT INTO `online_communication` (`id`, `company_id`, `country_id`, `message`, `date_added`, `time_interval`) VALUES
(53, 1, 210, 'third message.....\r\nand here..\r\n', '2015-05-01 11:05:31', '1'),
(61, 1, 103, 'third message.....\r\nand here.. \r\n', '2015-05-01 11:06:38', '1');

Note that the first insert has no space after two periods, but the second does. They look the same in the results because the difference is just a gap.

EDIT

These are unique posts by definition, so your request is fine. However, if you want to treat them as the same thing, you need to find a business rule for what defines them as the same thing.



For example, you can simply remove all spaces from the string and compare them like this:

SELECT DISTINCT REPLACE(message, ' ', '') FROM `online_communication`

Here is a Fiddle example .

Here are some useful string functions. Note that you cannot use it TRIM()

here because this function only removes spaces from the beginning or end of the string. In this example, it is in the middle, so it is REPLACE

more useful.

+2


source


One of your lines has extra space

(53, 1, 210, 'third message.....\r\nand here..\r\n', '2015-05-01 11:05:31', '1'),
(61, 1, 103, 'third message.....\r\nand here.. \r\n', '2015-05-01 11:06:38', '1');


Remove this and you will get the expected result.

SQL FIDDLE DEMO

+1


source


Are there spaces at the end of one of the instances of "third message ....."? According to the documentation , trailing spaces are not stripped from TEXT types. You can check with a query like this:

SELECT DISTINCT concat('[', message,  ']') FROM `online_communication`

You will be looking for something like this in the results:

[hello.. what up?]
[second message..]
[third message.....]
[third message.....   ]
0


source






More articles:


All Articles