-1

If have rows table like this

    id      content
    1       this five lenght is 12345
    2       this five lenght is 23456
    3       this six lenght is 234567
    4       this six lenght is 238567

Then when I want to select and group by content, it will result 

    SELECT * FROM table GROUP BY content

    1       this five lenght is 00000
    3       this six lenght is 000000

Is there a way to achieve this, that can replace number with zero in mysql query?

Many thanks in advance.

Al Kasih
  • 887
  • 5
  • 24
  • what is your mysql version – Fahmi Oct 16 '18 at 04:28
  • we always update things I think we have new version. wait I will check – Al Kasih Oct 16 '18 at 04:28
  • | innodb_version | 5.7.23 | | protocol_version | 10 | | slave_type_conversions | | | tls_version | TLSv1,TLSv1.1 | | version | 5.7.23-0ubuntu0.16.04.1 | | version_comment | (Ubuntu) | | version_compile_machine | x86_64 | | version_compile_os | Linux – Al Kasih Oct 16 '18 at 04:32
  • Possible duplicate of [How to do a regular expression replace in MySQL?](https://stackoverflow.com/questions/986826/how-to-do-a-regular-expression-replace-in-mysql) – Malay M Oct 16 '18 at 04:56

2 Answers2

1

Replace numbers with zero and then group them.

Example for replacement:

Select REGEXP_REPLACE('Stackoverflow 2456','[0-9]','0')
Stackoverflow 0000

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=db93de9c1c965090f46b4dbb1f48a63e

In your case:

SELECT REGEXP_REPLACE(CONTENT, '[0-9]','0') FROM TABLENAME GROUP BY REGEXP_REPLACE(CONTENT, '[0-9]','0')

Be careful tho this will probably do a full TABLE scan, so it will be really slow on large tables.

Hash
  • 4,647
  • 5
  • 21
  • 39
0

You can try with REGEXP_REPLACE() function - it'll work on mysql 8+ version

SELECT id, REGEXP_REPLACE (content,'[0-9]', '0') as content
FROM tablename GROUP BY REGEXP_REPLACE (content,'[0-9]', '0')
Fahmi
  • 37,315
  • 5
  • 22
  • 31
  • 1
    Ok, thank you. I will check first. I will be back if it works. Thanks – Al Kasih Oct 16 '18 at 04:34
  • function REGEXP_REPLACE doesnt exist – Al Kasih Oct 17 '18 at 04:02
  • @klaudia, what is your mysql version? – Fahmi Oct 17 '18 at 04:03
  • | innodb_version | 5.7.23 | | protocol_version | 10 | | slave_type_conversions | | | tls_version | TLSv1,TLSv1.1 | | version | 5.7.23-0ubuntu0.16.04.1 | | version_comment | (Ubuntu) | | version_compile_machine | x86_64 | | version_compile_os | Linux – Al Kasih Oct 17 '18 at 04:08