How to use GROUP BY on varchar?

Question

I am trying to group by two values because I want to remove duplicates from my database but somehow my group by clause doesn't want to work (the fields are in varchar). I also checked if there is a space or something more but there is none.

The server is a mariadb server, and I use a simple HeidiSQL Client

SELECT Player, COUNT(Player)
FROM Players
GROUP BY Player

I want it to simply group by so that I can remove duplicate values.

Here is the result I currently get :

SQL query

It's also the same with a SELECT CONCAT : SQL query

for ex: take a duplicate record `0rion59` then in a empty line insert quotes like ('') and paste that value and check whether there is space(white space or enter char). do it for both so result might be like '0rion59' and '0rion59 ' — James, Jul 24 '19 at 10:58
[**This answer**](https://stackoverflow.com/questions/688549/finding-duplicate-values-in-mysql) fits exactly what you're looking for, — Martin, Jul 24 '19 at 10:59
Possible duplicate of [Finding duplicate values in MySQL](https://stackoverflow.com/questions/688549/finding-duplicate-values-in-mysql) — Martin, Jul 24 '19 at 10:59
Also please [**see this topic**](https://stackoverflow.com/questions/11714534/mysql-database-with-unique-fields-ignored-ending-spaces) about whitespace in MySQL — Martin, Jul 24 '19 at 11:01
@Martin https://i.imgur.com/K1QUV6c.png still the same and i'll look at your topic 2 sec — Poli, Jul 24 '19 at 11:02
@Poli Then its better if you share the create table and sample data — James, Jul 24 '19 at 11:03
@Martin If i do a concat it still give me the same thing https://i.imgur.com/iaWf9hE.png — Poli, Jul 24 '19 at 11:06
It looks like those player names are using different characters. They *look* the same but are NOT identical. — Martin, Jul 24 '19 at 11:07
because i have a lot of them @Martin https://i.imgur.com/XD4e48B.png — Poli, Jul 24 '19 at 11:10
TRIM? @P.Salmon And this don't work for me c: https://stackoverflow.com/questions/39420442/remove-invisible-backspace-characters-from-mysql-data — Poli, Jul 24 '19 at 11:20
@P.Salmon i applied a UPDATE Players set Player = TRIM(Player); but it didn't seem to work — Poli, Jul 24 '19 at 11:22
https://stackoverflow.com/questions/7313803/mysql-remove-all-whitespaces-from-the-entire-column i tried this as well still nothing for me — Poli, Jul 24 '19 at 11:29
It must be some issue in table definition/creation because the query is working fine. You can verify it here: http://sqlfiddle.com/#!9/2b8bca/1/0 — TalESid, Jul 24 '19 at 11:33
Poli, please can you update (**edit**) your question with the [**Create Table Syntax**](https://stackoverflow.com/questions/11739014/how-to-generate-a-create-table-script-for-an-existing-table-in-phpmyadmin). Thank you. — Martin, Jul 24 '19 at 11:34

score 1 · Accepted Answer · answered Jul 24 '19 at 11:43

I founded my answer, the problem was that there was a line break at the other lines so mysql couldn't find it with my SELECT i created a bash script to fix it :

#!/bin/bash
export MYSQL_PWD="YOURPWD"


runSQL() {
        runSQLOutput=$(mysql -sN --user="YOURUSER" -h 127.0.0.1 -P 3306 --database="Factorio-Stats")
}

runSQL <<EOF
        SELECT Player
        FROM Players
EOF

mapfile -t Players < <(printf "%s\n" "$runSQLOutput")
        for Players in "${Players[@]}"
                do
                        test=`echo $Players | sed 's~[^[:alnum:]/]\+~~g'`
                        runSQL <<EOF
                                SELECT Id_Players
                                FROM Players
                                WHERE Player = "$Players"
EOF

                        runSQL <<EOF
                                UPDATE Players
                                SET Player="$test"
                                WHERE Id_Players="$runSQLOutput"
EOF
                        echo "updated $test"
done

I did a huge select in heidi sql and i saw that one record open with a window and not the other... took me time haha thanks for the help! — Poli, Jul 26 '19 at 11:52

How to use GROUP BY on varchar?

1 Answers1