How to correctly split a postal address into elements in MySQL?

Question

MySQL

Hello, I can’t figure out how to correctly atomize the mailing address through substring_index.
There is a line:
Arkhangelsk, st. Mayakovsky, d. 45
We select the street:

concat(substring_index(substring_index(substring_index(`houseProfile`.`adressHouse`,',',2),',',-(1)),' ',-(1)),' ',substring_index(substring_index(substring_index(`houseProfile`.`adressHouse`,',',2),',',-(1)),'.',1)) AS `улица`,

We get: Mayakovsky st
. But in the case of: Arkhangelsk, st. Belomorskaya flotilla, 12 - it turns out: flotilla st.
Is it possible to somehow process it so that in this case the same thing turned out as it should?
You can, of course, also through PHP, but in principle I want to here

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `test`
-- ----------------------------
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
  `adress` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of test
-- ----------------------------
INSERT INTO `test` VALUES ('г. Архангельск, ул. Полярная, д. 32');
INSERT INTO `test` VALUES ('г. Архангельск, ул. Советская, д. 22');
INSERT INTO `test` VALUES ('г. Архангельск, ул. Беломорской флотилии, д. 13');
INSERT INTO `test` VALUES ('г. Архангельск, ул. Терпких игрыстых вин, д. 13');
INSERT INTO `test` VALUES ('г. Архангельск, ул. тут-просто тест-такой, д. 13');

Answer 1

stackoverflow.com/questions/4078838/mysql-array-da...

Answer 2

Damn, this question has already been, but the answer has not been received:
https://toster.ru/q/260915

Answer 3

Use regular expressions:
www.mysql.ru/docs/man/Regexp.html