MySQL удаляет нечисловые символы для сравнения

Самый простой способ, который я могу придумать, - это использовать оператор MySQL REGEXP а-ля:

WHERE foo LIKE '1\D*2\D*3\D*4\D*5'

Это не особенно красиво, но MySQL не имеет функции preg_replace, поэтому я думаю, что это лучшее, что вы собираетесь получить.

Лично, если эти только числовые данные так важны, я бы сохранил отдельное поле, чтобы содержать удаленные данные. Это сделает ваш поиск намного быстрее, чем при поиске по регулярному выражению.

Хотя это некрасиво и показывает несовпадающие результаты, это помогает:

SELECT * FROM foo WHERE bar LIKE = '%1%2%3%4%5%'

Я все же хотел бы найти лучшее решение, аналогичное пункту в исходном вопросе.

Насколько я понимаю, замены регулярных выражений нет, но я нашел это решение;

--Create a table with numbers
DROP TABLE IF EXISTS ints;
CREATE TABLE ints (i INT UNSIGNED NOT NULL PRIMARY KEY);

INSERT INTO ints (i) VALUES
( 1), ( 2), ( 3), ( 4), ( 5), ( 6), ( 7), ( 8), ( 9), (10),
(11), (12), (13), (14), (15), (16), (17), (18), (19), (20);

--Then extract the numbers from the specified column
SELECT
    bar,
    GROUP_CONCAT(SUBSTRING(bar, i, 1) ORDER BY i SEPARATOR '')
FROM foo
JOIN ints ON i BETWEEN 1 AND LENGTH(bar)
WHERE
    SUBSTRING(bar, i, 1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')
GROUP BY bar;

У меня это работает, и я использую MySQL 5.0

Также я нашел этот место, который может помочь.

Насколько велика таблица с foo? Если он небольшой и скорость действительно не имеет значения, вы можете вытащить идентификатор строки и foo, перебрать его, используя функции замены PHP для сравнения, а затем получить нужную информацию по номеру строки.

Конечно, если таблица будет слишком большой, это не сработает.

Здесь нет замены регулярного выражения, только обычная строка REPLACE ().

В MySQL есть оператор REGEXP, но это всего лишь тестер совпадений, а не заменитель, поэтому вам придется вывернуть логику наизнанку:

SELECT * FROM foo WHERE bar REGEXP '[^0-9]*1[^0-9]*2[^0-9]*3[^0-9]*4[^0-9]*5[^0-9]*';

Это похоже на вашу версию с LIKE, но более точно соответствует. Оба будут работать одинаково плохо, и потребуется полное сканирование таблицы без индексов.

У меня аналогичная ситуация, когда продукты сопоставляются со штрих-кодами, где штрих-код иногда не хранит буквенно-цифровые символы, поэтому при поиске 1022234 необходимо найти 102.2234 в БД.

В конце концов, я просто добавил новое поле reference_number в таблицы продуктов и заставил php вырезать не буквенные числа в product_number, чтобы заполнять reference_number всякий раз, когда добавляются новые продукты.

Вам нужно будет выполнить одноразовое сканирование таблицы, чтобы создать все поля reference_number для существующих продуктов.

Затем вы можете настроить свой индекс, даже если скорость не является фактором для этой операции, все равно рекомендуется поддерживать хорошую работу базы данных, чтобы этот запрос не утомлял ее и не замедлял другие запросы.

В этом сообщении блога подробно описано, как удалить нечисловые символы из строки с помощью функции MySQL:

SELECT NumericOnly("asdf11asf");

возвращает 11

http://venerableagents.wordpress.com/2011/01/29/mysql-numeric-functions/

Я понимаю, что это древняя тема, но после поиска этой проблемы я не смог найти простого решения (я видел почтенных агентов, но думаю, что это более простое решение), поэтому вот функция, которую я написал, похоже, работает довольно хорошо.

DROP FUNCTION IF EXISTS STRIP_NON_DIGIT;
DELIMITER $$
CREATE FUNCTION STRIP_NON_DIGIT(input VARCHAR(255))
   RETURNS VARCHAR(255)
BEGIN
   DECLARE output   VARCHAR(255) DEFAULT '';
   DECLARE iterator INT          DEFAULT 1;
   WHILE iterator < (LENGTH(input) + 1) DO
      IF SUBSTRING(input, iterator, 1) IN ( '0', '1', '2', '3', '4', '5', '6', '7', '8', '9' ) THEN
         SET output = CONCAT(output, SUBSTRING(input, iterator, 1));
      END IF;
      SET iterator = iterator + 1;
   END WHILE;
   RETURN output;
END
$$

Я наткнулся на это решение. Верхний ответ user1467716 будет работать в phpMyAdmin с небольшим изменением: добавьте второй тег-разделитель в конец кода.

Версия phpMyAdmin - 4.1.14; MySQL версии 5.6.20

Я также добавил ограничитель длины, используя

DECLARE count INT DEFAULT 0; в объявлениях

AND count < 5 в заявлении WHILE

SET COUNT=COUNT+1; в заявлении IF

Окончательная форма:

DROP FUNCTION IF EXISTS STRIP_NON_DIGIT;
DELIMITER $$
CREATE FUNCTION STRIP_NON_DIGIT(input VARCHAR(255))
   RETURNS VARCHAR(255)
BEGIN
   DECLARE output   VARCHAR(255) DEFAULT '';
   DECLARE iterator INT          DEFAULT 1;
   DECLARE count INT DEFAULT 0;
   WHILE iterator < (LENGTH(input) + 1) AND count < 5 DO --limits to 5 chars
      IF SUBSTRING(input, iterator, 1) IN ( '0', '1', '2', '3', '4', '5', '6', '7', '8', '9' ) THEN
         SET output = CONCAT(output, SUBSTRING(input, iterator, 1));
         SET COUNT=COUNT+1;
      END IF;
      SET iterator = iterator + 1;
   END WHILE;
   RETURN output;
END
$$
DELIMITER $$ --added this

ответ выше, получивший наибольшее количество голосов, не самый быстрый. Полное уважение к ним за то, что они дали рабочее предложение, чтобы отскочить!

Это улучшенная версия:

DELIMITER ;;
DROP FUNCTION IF EXISTS `STRIP_NON_DIGIT`;;

CREATE DEFINER=`root`@`localhost` FUNCTION `STRIP_NON_DIGIT`(input VARCHAR(255)) RETURNS VARCHAR(255) CHARSET utf8
READS SQL DATA
BEGIN
   DECLARE output    VARCHAR(255) DEFAULT '';
   DECLARE iterator  INT          DEFAULT 1;
   DECLARE lastDigit INT          DEFAULT 1;
   DECLARE len       INT;

   SET len = LENGTH(input) + 1;
   WHILE iterator < len DO
      -- skip past all digits
      SET lastDigit = iterator;
      WHILE ORD(SUBSTRING(input, iterator, 1)) BETWEEN 48 AND 57 AND iterator < len DO
         SET iterator = iterator + 1;
      END WHILE;

      IF iterator != lastDigit THEN
         SET output = CONCAT(output, SUBSTRING(input, lastDigit, iterator - lastDigit));
      END IF;

      WHILE ORD(SUBSTRING(input, iterator, 1)) NOT BETWEEN 48 AND 57 AND iterator < len DO
         SET iterator = iterator + 1;
      END WHILE;
   END WHILE;

   RETURN output;
END;;

Тестирование 5000 раз на тестовом сервере:

-- original
Execution Time : 7.389 sec
Execution Time : 7.257 sec
Execution Time : 7.506 sec

-- ORD between not string IN
Execution Time : 4.031 sec

-- With less substrings
Execution Time : 3.243 sec
Execution Time : 3.415 sec
Execution Time : 2.848 sec

попробуйте этот пример. это используется для телефонных номеров, однако вы можете изменить его по своему усмотрению.

   -- function removes non numberic characters from input
-- returne only the numbers in the string

CREATE DEFINER =`root`@`localhost` FUNCTION `remove_alpha`(inputPhoneNumber VARCHAR(50))
  RETURNS VARCHAR(50)
  CHARSET latin1
DETERMINISTIC
  BEGIN


    DECLARE inputLenght INT DEFAULT 0;
    -- var for our iteration 
    DECLARE counter INT DEFAULT 1;
    -- if null is passed, we still return an tempty string
    DECLARE sanitizedText VARCHAR(50) DEFAULT '';
    -- holder of each character during the iteration
    DECLARE oneChar VARCHAR(1) DEFAULT '';


    -- we'll process only if it is not null.
    IF NOT ISNULL(inputPhoneNumber)
    THEN
      SET inputLenght = LENGTH(inputPhoneNumber);
      WHILE counter <= inputLenght DO
        SET oneChar = SUBSTRING(inputPhoneNumber, counter, 1);
        IF (oneChar REGEXP ('^[0-9]+$'))
        THEN
          SET sanitizedText = Concat(sanitizedText, oneChar);
        END IF;

        SET counter = counter + 1;
      END WHILE;
    END IF;

    RETURN sanitizedText;
      END

использовать эту пользовательскую функцию (UDF). допустим, у вас есть столбец с телефонными номерами:

col1
(513)983-3983
1-838-338-9898
phone983-889-8383

select remove_alpha(col1) from mytable

Результат будет;

5139833983
18383389898
9838898383

Вы можете легко делать то, что хотите, с REGEXP_REPLACE (совместим с MySQL 8+ и MariaDB 10.0.5+)

REGEXP_REPLACE(expr, pat, repl[, pos[, occurrence[, match_type]]])

Replaces occurrences in the string expr that match the regular expression specified by the pattern pat with the replacement string repl, and returns the resulting string. If expr, pat, or repl is NULL, the return value is NULL.

Go to REGEXP_REPLACE doc: MySQL or MariaDB

Попробуй это:

SELECT REGEXP_REPLACE('123asd12333', '[a-zA-Z]+', '');

Выход:

12312333

думал, что поделюсь этим, так как я построил его на функции отсюда. Я переставил просто так, чтобы мне было легче читать (я просто серверная сторона).

Вы вызываете его, передавая имя таблицы и имя столбца, чтобы удалить все существующие нечисловые символы из этого столбца. Я унаследовал много плохих структур таблиц, которые помещают тонну полей типа int как varchar, поэтому мне нужен был способ быстро очистить их, прежде чем я смогу изменить столбец на целое число.

drop procedure if exists strip_non_numeric_characters;
DELIMITER ;;

CREATE PROCEDURE `strip_non_numeric_characters`(
    tablename varchar(100)
    ,columnname varchar(100)
    )
BEGIN

-- =============================================
-- Author:      <Author,,David Melton>
-- Create date: <Create Date,,2/26/2019>
-- Description: <Description,,loops through data and strips out the bad characters in whatever table and column you pass it>
-- =============================================

#this idea was generated from the idea STRIP_NON_DIGIT function
#https://stackoverflow.com/questions/287105/mysql-strip-non-numeric-characters-to-compare

declare input,output varchar(255);
declare iterator,lastDigit,len,counter int;
declare date_updated varchar(100);

select column_name 
    into date_updated
    from information_schema.columns 
    where table_schema = database() 
    and extra rlike 'on update CURRENT_TIMESTAMP'
    and table_name = tablename
    limit 1;

#only goes up to 255 so people don't run this for a longtext field
#just to be careful, i've excluded columns that are part of keys, that could potentially mess something else up
set @find_column_length = 
concat("select character_maximum_length
    into @len
    from information_schema.columns
    where table_schema = '",database(),"'
    and column_name = '",columnname,"'
    and table_name = '",tablename,"'
    and length(ifnull(character_maximum_length,100)) < 255
    and data_type in ('char','varchar')
    and column_key = '';");

prepare stmt from @find_column_length;
execute stmt;
deallocate prepare stmt;

set counter = 1;        
set len = @len;

while counter <= ifnull(len,1) DO

    #this just removes it by putting all the characters before and after the character i'm looking at
    #you have to start at the end of the field otherwise the lengths don't stay in order and you have to run it multiple times
    set @update_query = 
    concat("update `",tablename,"`
        set `",columnname,"` = concat(substring(`",columnname,"`,1,",len - counter,"),SUBSTRING(`",columnname,"`,",len - counter,",",counter - 1,"))
        ",if (date_updated is not null,concat(",`",date_updated,"` = `",date_updated,"`
        "),''),
        "where SUBSTRING(`",columnname,"`,",len - counter,", 1) not REGEXP '^[0-9]+$';");

    prepare stmt from @update_query;
    execute stmt;
    deallocate prepare stmt;

    set counter = counter + 1;

end while;

END ;;
DELIMITER ;

В MySQL 8.0+ есть новая встроенная функция под названием REGEXP_REPLACE. Чистое решение этого вопроса:

SELECT * FROM foo WHERE REGEXP_REPLACE(bar,'[^0-9]+',"") = '12345'

Чтобы найти числа, которые соответствуют определенному числовому шаблону в строке, сначала удалите все алфавиты и специальные символы таким же образом, как показано ниже, затем преобразуйте значение в целое число, а затем выполните поиск

SELECT * 
FROM   foo 
WHERE  Convert(Regexp_replace(bar, '[a-zA-Z]+', ''), signed) = 12345