mysql delete one cyrillic word select one cyrillic word

Question

mysql>show full columns from  bpsw;
+--------+------------------+-----------+------+-----+---------+----------------+---------------------------------+---------+
| Field  | Type             | Collation | Null | Key | Default | Extra          | Privileges                      | Comment |
+--------+------------------+-----------+------+-----+---------+----------------+---------------------------------+---------+
| bpswid | int(10) unsigned | NULL      | NO   | PRI | NULL    | auto_increment | select,insert,update,references |         |
| badpsw | varchar(128)     | utf8_bin  | NO   | UNI | NULL    |                | select,insert,update,references |         |
+--------+------------------+-----------+------+-----+---------+----------------+---------------------------------+---------+

dont say me about not null and default null :)

mysql> SELECT USER(), CHARSET(USER()), COLLATION(USER());
+----------------+-----------------+-------------------+
| USER()         | CHARSET(USER()) | COLLATION(USER()) |
+----------------+-----------------+-------------------+
| root@localhost | utf8            | utf8_general_ci   |
+----------------+-----------------+-------------------+
1 row in set (0.00 sec)

---

table contents:

mysql> select * from bpsw limit X offset XXX ;
+--------+------------------------+
| bpswid | badpsw                 |
+--------+------------------------+
| 495883 | by all manner of means |
| 495884 | by all means           |
| 495885 | by all odds            |
| 495886 | by an ace              |
| 495887 | by an iota             |
| 495888 | by and by              |
| 495889 | by and large           |
| 495890 | by any chance          |
| 495891 | by any manner of means |
| 495892 | by any means           |
+--------+------------------------+
...
|   94950 | яростных                                                    |
|    1599 | ярь-медянка                                                 |
|    1600 | ястреб-перепелятник                                         |
|    1601 | ястреб-тетеревятник                                         |
|   94999 | яфетический                                                 |
|    1603 | яхт-клуб                                                    |
|    1604 | яхт-клуба                                                   |
...
|    1938 | яванский желоб                                              |
|    1939 | яванское море                                               |
|   94690 | еще какое-то слово                                          |
|    1940 | яде-бузен залив                                             |
|   94751 | ядерного                                                    |
|   94755 | раз два-три                                                 | 

---

need: select or remove from the table cyrillic words.

it is necessary to delete the rows in which ONE Cyrillic word WITHOUT digit, without special chars, without chars of punctuation.

Condition for removal: '^[а-я]+[а-я]$+'

select * from bpsw where badpsw regexp '^[a-z]+[a-z]$+';

With English words there are no problems, but with the Cyrillic alphabet I will not understand HOW it make.

I think that it is necessary to specify a collate?

UPD: mysql regex utf-8 characters ?

can here i need to look at the meanings of the Cyrillic symbols in the byte representation?

Answer 1

It's easy:

select * from bpsw where badpsw regexp '^[абвгдеёжзийклмнопрстуфхцчшщъыьэюя]+$'; 

---

https://linux.org.ua/index.php?topic=11272.msg201662#msg201662

Answer 2

SELECT USER(), CHARSET(USER()), COLLATION(USER()); says virtually nothing. USER() returns a string that is defined by the table that "user" comes from. It is always utf8.

Perhaps what you want is

mysql> SHOW VARIABLES LIKE 'char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

REGEXP works only with bytes, not non-ascii characters, such as the 2-byte Cyrillic utf8 characters. (Recent MariaDB versions can handle such.)

Instead of SHOW FULL COLUMNS..., use SHOW CREATE TABLE... -- that will make it more obvious whether the columns are NOT NULL or NULL.

This can discover Cyrillic strings:

WHERE HEX(col) REGEXP '^(..)*D[0-4]'

because, in utf8, all Cyrillic characters are 2 bytes, the first of which is HEX D0, D1, D2, D3, or D4.

SELECT HEX('яростных') REGEXP '^(..)*D[0-4]'; --> 1

You want to "remove" Cyrillic? If you want to remove the rows, then a DELETE with that WHERE will suffice. If you want to edit the text, that will take application code (unless you have MariaDB and can use REGEXP_REPLACE() ).

Have I addressed all the issues?

Edit

To delete the row where col1 contains just a single Cyrillic word,

 DELETE FROM tbl
     WHERE HEX(col1) REGEXP '^(D[0-4]..)+$';

Answer 3

Try to take a look at this:

Is this the Record you want to delete?

 select  bpswid,badpsw from  Your_Table
 where  badpsw like '%б%' or  badpsw like '%в%'
 or  badpsw like '%г%'
 or   badpsw like '%д%'
 or   badpsw like '%ё%'
 or  badpsw like '%ж%'
 or  badpsw like '%з%'
 or  badpsw like '%и%'
 or  badpsw like '%й%'
 or  badpsw like '%к%'
 or  badpsw like '%л%'
 or  badpsw like '%м%'
 or  badpsw like '%н%'
 or  badpsw like '%п%'
 or  badpsw like '%т%'
 or  badpsw like '%ф%'
 or  badpsw like '%у%'
 or  badpsw like '%ц%'
 or  badpsw like '%ч%'
 or  badpsw like '%ш%'
 or  badpsw like '%щ%'
 or  badpsw like '%ъ%'
 or  badpsw like '%ы%'
 or  badpsw like '%ь%'
 or  badpsw like '%э%'
 or  badpsw like '%ю%'
 or  badpsw like '%я%'
 or  badpsw like '%с%'
 or  badpsw like '%р%'
 or  badpsw like '%х%'
 or  badpsw like '%е%'
or  badpsw like '%о%'

Result:

94950   яростных
1599    ярь-медянка
1600    ястреб-перепелятник
1601    ястреб-тетеревятник
94999   яфетический
1603    яхт-клуб
1604    яхт-клуба
1938    яванский желоб
1939    яванское море
94690   еще какое-то слово
1940    яде-бузен залив
94751   ядерного
94755   раз два-три

if not then just remove the other in like condition. then proceed deletion of record.