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

Я ищу, чтобы найти записи в таблице, которые соответствуют определенному числу, которое вводит пользователь. Таким образом, пользователь может ввести 12345, но это может быть 123zz4-5 в базе данных.

Я предполагаю, что что-то подобное будет работать, если функции PHP работают в MySQL.

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

Что эквивалентная функция или способ сделать это с помощью MySQL?

Ответ 1

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

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

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

Ответ 2

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

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
$$

Ответ 3

Нет regexp replace, только простая строка 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, но соответствует более точно. Оба будут работать одинаково плохо, требуя полного сканирования таблицы без индексов.

Ответ 4

Самый высокий ответ (@user1467716) не самый быстрый. Полные слава им за то, что они дали рабочее предложение отскакивать!

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

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

Ответ 5

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

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

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

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

Ответ 6

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

SELECT NumericOnly("asdf11asf");

возвращает 11

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

Ответ 7

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

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

Заменяет вхождения в строке expr, которые соответствуют регулярному выражению, указанному в шаблоне pat, на строку замены repl и возвращает результирующую строку. Если expr, pat или repl имеют значение NULL, возвращаемое значение равно NULL.

Перейти к документу REGEXP_REPLACE: MySQL или MariaDB

Попытайся:

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

Выход:

12312333

Ответ 8

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

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

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

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

Ответ 9

Я столкнулся с этим решением. Верхний ответ пользователя1467716 будет работать в 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

Ответ 10

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

--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

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

Ответ 11

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

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

Ответ 12

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

   -- 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

Ответ 13

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

Вы вызываете его, передавая имя таблицы и имя столбца, чтобы удалить все существующие нечисловые символы из этого столбца. Я унаследовал много плохих табличных структур, которые помещают тонну полей 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 ;

Ответ 14

На MySQL 8. 0+ есть новая встроенная функция с именем REGEXP_REPLACE. Чистое решение этого вопроса было бы:

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