Как удалить все не буквенно-цифровые символы из строки в MySQL?
Я работаю над процедурой, которая сравнивает строки, но для большей эффективности мне нужно удалить все символы, которые не являются буквами или цифрами.
Я использую несколько REPLACE функции сейчас, но, может быть, есть более быстрое и приятное решение?
17 ответов
Ни один из этих ответов не работал для меня. Я должен был создать свою собственную функцию alphanum, которая освободила меня от символов:
DROP FUNCTION IF EXISTS alphanum;
DELIMITER |
CREATE FUNCTION alphanum( str CHAR(255) ) RETURNS CHAR(255) DETERMINISTIC
BEGIN
DECLARE i, len SMALLINT DEFAULT 1;
DECLARE ret CHAR(255) DEFAULT '';
DECLARE c CHAR(1);
SET len = CHAR_LENGTH( str );
REPEAT
BEGIN
SET c = MID( str, i, 1 );
IF c REGEXP '[[:alnum:]]' THEN
SET ret=CONCAT(ret,c);
END IF;
SET i = i + 1;
END;
UNTIL i > len END REPEAT;
RETURN ret;
END |
DELIMITER ;
Теперь я могу сделать:
select 'This works finally!', alphanum('This works finally!');
и я получаю:
+---------------------+---------------------------------+
| This works finally! | alphanum('This works finally!') |
+---------------------+---------------------------------+
| This works finally! | Thisworksfinally |
+---------------------+---------------------------------+
1 row in set (0.00 sec)
Ура!
С точки зрения производительности (и при условии, что вы читаете больше, чем пишете)
Я думаю, что лучшим способом было бы предварительно рассчитать и сохранить урезанную версию столбца. Таким образом, вы выполняете преобразование меньше.
Затем вы можете поместить индекс в новый столбец и получить базу данных, которая сделает всю работу за вас.
Начиная с MySQL 8.0 вы можете использовать регулярное выражение для удаления не буквенно-цифровых символов из строки. Есть метод REGEXP_REPLACE
Вот код для удаления не буквенно-цифровых символов:
UPDATE {table} SET {column} = REGEXP_REPLACE({column}, '[^0-9a-zA-Z ]', '')
SELECT teststring REGEXP '[[:alnum:]]+';
SELECT * FROM testtable WHERE test REGEXP '[[:alnum:]]+';
См.: http://dev.mysql.com/doc/refman/5.1/en/regexp.html
Прокрутите вниз до раздела, который говорит: [:character_class:]
Если вы хотите манипулировать строками, самый быстрый способ будет использовать str_udf, смотрите:
https://github.com/hholzgra/mysql-udf-regexp
Прямое и проверенное решение для латинских и кириллических символов:
DELIMITER //
CREATE FUNCTION `remove_non_numeric_and_letters`(input TEXT)
RETURNS TEXT
BEGIN
DECLARE output TEXT 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', 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l', 'm', 'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y', 'z', 'А', 'Б', 'В', 'Г', 'Д', 'Е', 'Ж', 'З', 'И', 'Й', 'К', 'Л', 'М', 'Н', 'О', 'П', 'Р', 'С', 'Т', 'У', 'Ф', 'Х', 'Ц', 'Ч', 'Ш', 'Щ', 'Ъ', 'Ы', 'Ь', 'Э', 'Ю', 'Я', 'а', 'б', 'в', 'г', 'д', 'е', 'ж', 'з', 'и', 'й', 'к', 'л', 'м', 'н', 'о', 'п', 'р', 'с', 'т', 'у', 'ф', 'х', 'ц', 'ч', 'ш', 'щ', 'ъ', 'ы', 'ь', 'э', 'ю', 'я')
THEN
SET output = CONCAT(output, SUBSTRING(input, iterator, 1));
END IF;
SET iterator = iterator + 1;
END WHILE;
RETURN output;
END //
DELIMITER ;
Использование:
-- outputs "hello12356"
SELECT remove_non_numeric_and_letters('hello - 12356-привет ""]')
Основано на ответе Райана Шиллингтона, измененном для работы со строками длиной более 255 символов и сохранения пробелов в исходной строке.
К вашему сведению lower(str) в конце.
Я использовал это для сравнения строк:
DROP FUNCTION IF EXISTS spacealphanum;
DELIMITER $$
CREATE FUNCTION `spacealphanum`( str TEXT ) RETURNS TEXT CHARSET utf8
BEGIN
DECLARE i, len SMALLINT DEFAULT 1;
DECLARE ret TEXT DEFAULT '';
DECLARE c CHAR(1);
SET len = CHAR_LENGTH( str );
REPEAT
BEGIN
SET c = MID( str, i, 1 );
IF c REGEXP '[[:alnum:]]' THEN
SET ret=CONCAT(ret,c);
ELSEIF c = ' ' THEN
SET ret=CONCAT(ret," ");
END IF;
SET i = i + 1;
END;
UNTIL i > len END REPEAT;
SET ret = lower(ret);
RETURN ret;
END $$
DELIMITER ;
Самый быстрый способ, который мне удалось найти (и использовать), - с помощью convert ().
из док. CONVERT() с USING используется для преобразования данных между различными наборами символов.
Пример:
convert(string USING ascii)
В вашем случае правильный набор символов будет определяться самостоятельно
ПРИМЕЧАНИЕ от Док. ИСПОЛЬЗОВАНИЕ формы CONVERT() доступно с 4.1.0.
Будьте осторожны, такие символы, как "или", рассматриваются как альфа в MySQL. Лучше использовать что-то вроде:
ЕСЛИ c МЕЖДУ 'a' И 'z' ИЛИ c МЕЖДУ 'A' И 'Z' ИЛИ c МЕЖДУ '0' И '9' ИЛИ c = '-' THEN
Я написал этот UDF. Тем не менее, он обрезает только специальные символы в начале строки. Он также преобразует строку в нижний регистр. При желании вы можете обновить эту функцию.
DELIMITER //
DROP FUNCTION IF EXISTS DELETE_DOUBLE_SPACES//
CREATE FUNCTION DELETE_DOUBLE_SPACES ( title VARCHAR(250) )
RETURNS VARCHAR(250) DETERMINISTIC
BEGIN
DECLARE result VARCHAR(250);
SET result = REPLACE( title, ' ', ' ' );
WHILE (result <> title) DO
SET title = result;
SET result = REPLACE( title, ' ', ' ' );
END WHILE;
RETURN result;
END//
DROP FUNCTION IF EXISTS LFILTER//
CREATE FUNCTION LFILTER ( title VARCHAR(250) )
RETURNS VARCHAR(250) DETERMINISTIC
BEGIN
WHILE (1=1) DO
IF( ASCII(title) BETWEEN ASCII('a') AND ASCII('z')
OR ASCII(title) BETWEEN ASCII('A') AND ASCII('Z')
OR ASCII(title) BETWEEN ASCII('0') AND ASCII('9')
) THEN
SET title = LOWER( title );
SET title = REPLACE(
REPLACE(
REPLACE(
title,
CHAR(10), ' '
),
CHAR(13), ' '
) ,
CHAR(9), ' '
);
SET title = DELETE_DOUBLE_SPACES( title );
RETURN title;
ELSE
SET title = SUBSTRING( title, 2 );
END IF;
END WHILE;
END//
DELIMITER ;
SELECT LFILTER(' !@#$%^&*()_+1a b');
Также вы можете использовать регулярные выражения, но для этого необходимо установить расширение MySql.
Это можно сделать с помощью функции замены регулярных выражений, которую я опубликовал в другом ответе и опубликовал здесь. Возможно, это не самое эффективное решение и может показаться излишним для этой работы, но, как швейцарский армейский нож, оно может пригодиться по другим причинам.
Это можно увидеть в действии, удалив все не алфавитно-цифровые символы в этой онлайн-демонстрации Rextester.
SQL (исключая код функции для краткости):
SELECT txt,
reg_replace(txt,
'[^a-zA-Z0-9]+',
'',
TRUE,
0,
0
) AS `reg_replaced`
FROM test;
У меня была похожая проблема с попыткой сопоставить фамилии в нашей базе данных, которые немного отличались. Например, иногда люди вводили имя того же человека, что и "Макдональд", а также "Макдональд" или "Сент-Джон" и "Сент-Джон".
Вместо того, чтобы пытаться преобразовать данные Mysql, я решил проблему, создав функцию (в PHP), которая будет принимать строку и создавать регулярное выражение только для альфы:
function alpha_only_regex($str) {
$alpha_only = str_split(preg_replace('/[^A-Z]/i', '', $str));
return '^[^a-zA-Z]*'.implode('[^a-zA-Z]*', $alpha_only).'[^a-zA-Z]*$';
}
Теперь я могу искать в базе данных с помощью запроса:
$lastname_regex = alpha_only_regex($lastname);
$query = "SELECT * FROM my_table WHERE lastname REGEXP '$lastname_regex';
Пока что единственный альтернативный подход, менее сложный, чем другие ответы здесь, состоит в том, чтобы определить полный набор специальных символов столбца, то есть всех специальных символов, которые используются в этом столбце в данный момент, а затем выполнить последовательную замену все эти символы, например
update pages set slug = lower(replace(replace(replace(replace(name, ' ', ''), '-', ''), '.', ''), '&', '')); # replacing just space, -, ., & only
,
Это рекомендуется только на известном наборе данных, в противном случае некоторые специальные символы тривиально пропускают с помощью подхода черного списка вместо подхода белого списка.
Очевидно, что самым простым способом является предварительная проверка данных за пределами SQL из-за отсутствия надежного встроенного белого списка (например, с помощью замены регулярных выражений).
Нужно заменить не буквенно-цифровые символы, а не удалить не буквенно-цифровые символы, поэтому я создал это на основе алфавита Райана Шиллингтона. Работает для строк длиной до 255 символов
DROP FUNCTION IF EXISTS alphanumreplace;
DELIMITER |
CREATE FUNCTION alphanumreplace( str CHAR(255), d CHAR(32) ) RETURNS CHAR(255)
BEGIN
DECLARE i, len SMALLINT DEFAULT 1;
DECLARE ret CHAR(32) DEFAULT '';
DECLARE c CHAR(1);
SET len = CHAR_LENGTH( str );
REPEAT
BEGIN
SET c = MID( str, i, 1 );
IF c REGEXP '[[:alnum:]]' THEN SET ret=CONCAT(ret,c);
ELSE SET ret=CONCAT(ret,d);
END IF;
SET i = i + 1;
END;
UNTIL i > len END REPEAT;
RETURN ret;
END |
DELIMITER ;
Пример:
select 'hello world!',alphanum('hello world!'),alphanumreplace('hello world!','-');
+--------------+--------------------------+-------------------------------------+
| hello world! | alphanum('hello world!') | alphanumreplace('hello world!','-') |
+--------------+--------------------------+-------------------------------------+
| hello world! | helloworld | hello-world- |
+--------------+--------------------------+-------------------------------------+
Вам нужно будет добавить функцию alphanum отдельно, если вы этого хотите, я просто привел ее здесь для примера.
Мне нужно было получить только буквенные символы строки в процедуре, и сделал:
SET @source = "whatever you want";
SET @target = '';
SET @i = 1;
SET @len = LENGTH(@source);
WHILE @i <= @len DO
SET @char = SUBSTRING(@source, @i, 1);
IF ((ORD(@char) >= 65 && ORD(@char) <= 90) || (ORD(@char) >= 97 && ORD(@char) <= 122)) THEN
SET @target = CONCAT(@target, @char);
END IF;
SET @i = @i + 1;
END WHILE;
Я попробовал несколько решений, но в конце использовал replace, Мой набор данных - это номера деталей, и я точно знаю, чего ожидать. Но для здравого смысла я использовал PHP для построения длинного запроса:
$dirty = array(' ', '-', '.', ',', ':', '?', '/', '!', '&', '@');
$query = 'part_no';
foreach ($dirty as $dirt) {
$query = "replace($query,'$dirt','')";
}
echo $query;
Это выводит то, что я использовал, чтобы получить головную боль от:
replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(part_no,' ',''),'-',''),'.',''),',',''),':',''),'?',''),'/',''),'!',''),'&',''),'@','')
Функция alphanum (самоотвеченная) имеет ошибку, но я не знаю почему. Для текста "cas synt ls 75W140 1L" верните "cassyntls75W1401", в конце "L" не хватает как-нибудь.
Сейчас пользуюсь
delimiter //
DROP FUNCTION IF EXISTS alphanum //
CREATE FUNCTION alphanum(prm_strInput varchar(255))
RETURNS VARCHAR(255)
DETERMINISTIC
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE v_char VARCHAR(1);
DECLARE v_parseStr VARCHAR(255) DEFAULT ' ';
WHILE (i <= LENGTH(prm_strInput) ) DO
SET v_char = SUBSTR(prm_strInput,i,1);
IF v_char REGEXP '^[A-Za-z0-9]+$' THEN
SET v_parseStr = CONCAT(v_parseStr,v_char);
END IF;
SET i = i + 1;
END WHILE;
RETURN trim(v_parseStr);
END
//
(найдено в Google)
Вероятно, глупое предложение по сравнению с другими:
if(!preg_match("/^[a-zA-Z0-9]$/",$string)){
$sortedString=preg_replace("/^[a-zA-Z0-9]+$/","",$string);
}
Если вы используете php, то....
try{
$con = new PDO ("mysql:host=localhost;dbname=dbasename","root","");
}
catch(PDOException $e){
echo "error".$e-getMessage();
}
$select = $con->prepare("SELECT * FROM table");
$select->setFetchMode(PDO::FETCH_ASSOC);
$select->execute();
while($data=$select->fetch()){
$id = $data['id'];
$column = $data['column'];
$column = preg_replace("/[^a-zA-Z0-9]+/", " ", $column); //remove all special characters
$update = $con->prepare("UPDATE table SET column=:column WHERE id='$id'");
$update->bindParam(':column', $column );
$update->execute();
// echo $column."<br>";
}