Как удалить все не буквенно-цифровые символы из строки в MySQL?
Я работаю над рутиной, которая сравнивает строки, но для повышения эффективности мне нужно удалить все символы, которые не являются буквами или цифрами.
Теперь я использую несколько функций REPLACE, но, возможно, есть более быстрое и приятное решение?
Ответы
Ответ 1
Ни один из этих ответов не работал у меня. Мне пришлось создать свою собственную функцию 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)
Ура!
Ответ 2
С точки зрения производительности,
(и в предположении, что вы читаете больше, чем вы пишете)
Я думаю, что лучший способ - предварительно вычислить и сохранить разделенную версию столбца,
Таким образом, вы уменьшите преобразование.
Затем вы можете поместить индекс в новый столбец и получить базу данных для выполнения этой работы.
Ответ 3
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
Ответ 4
На основе ответа Райана Шиллингтона, измененного для работы со строками длиной более 255 символов и сохранением пробелов из исходной строки.
FYI в конце концов 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 ;
Ответ 5
Прямое и бочкообразное решение для латинских и кириллических символов:
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-привет ""]')
Ответ 6
Самый быстрый способ, которым я смог найти (и использовать), - это convert().
из Doc. CONVERT() с USING используется для преобразования данных между различными наборами символов.
Пример:
convert(string USING ascii)
В вашем случае правильный набор символов будет самостоятельно определено
ПРИМЕЧАНИЕ от Док. Форма USING CONVERT() доступна с 4.1.0.
Ответ 7
Я написал этот 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(' [email protected]#$%^&*()_+1a b');
Кроме того, вы можете использовать регулярные выражения, но для этого требуется установка расширения MySql.
Ответ 8
Будьте осторожны, символы, подобные or ", считаются альфами MySQL.
Лучше использовать что-то вроде:
IF c МЕЖДУ 'a' И 'z' ИЛИ c МЕЖДУ 'A' И 'Z' ИЛИ c МЕЖДУ '0' И '9' ИЛИ c = '-' THEN
Ответ 9
Это можно сделать с помощью функции замены регулярных выражений, которую я опубликовал в другом ответе, и опубликовал блог о . Возможно, это не самое эффективное решение и может выглядеть излишним для работы в руке - но, как швейцарский армейский нож, он может пригодиться по другим причинам.
В действии можно удалить все не-буквенно-цифровые символы в эту демонстрационную версию Rextester.
SQL (исключая код функции для краткости):
SELECT txt,
reg_replace(txt,
'[^a-zA-Z0-9]+',
'',
TRUE,
0,
0
) AS `reg_replaced`
FROM test;
Ответ 10
До сих пор единственным альтернативным подходом, который был менее сложным, чем другие ответы здесь, является определение полного набора специальных символов столбца, то есть всех специальных символов, которые используются в этом столбце на данный момент, а затем выполните последовательная замена всех этих символов, например
update pages set slug = lower(replace(replace(replace(replace(name, ' ', ''), '-', ''), '.', ''), '&', '')); # replacing just space, -, ., & only
.
Это полезно только для известного набора данных, иначе это тривиальным для некоторых специальных символов, чтобы проскользнуть мимо с помощью черный список, а не белый вариант.
Очевидно, что самый простой способ - предварительная проверка данных вне sql из-за отсутствия надежного встроенного белого списка (например, с помощью замены регулярного выражения).
Ответ 11
У меня была аналогичная проблема с попыткой сопоставить имена в нашей базе данных, которые были немного разными. Например, иногда люди входили в одно и то же имя человека, как "Макдональд", а также "Мак Дональд", или "Святой Иоанн" и "Святой Иоанн".
Вместо того, чтобы пытаться преобразовать данные 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';
Ответ 12
Мне нужно было получить только буквенные символы строки в процедуре и сделал:
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;
Ответ 13
Требуется заменить не буквенно-цифровые символы, а не удалять не буквенно-цифровые символы, поэтому я создал это на основе Ryan Shillington alphanum. Работает для строк длиной до 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 отдельно, если вы этого хотите, я просто приведу ее здесь для примера.
Ответ 14
Начиная с MySQL 8.0, вы можете использовать регулярные выражения для удаления не алфавитно-цифровых символов из строки. Существует метод REGEXP_REPLACE
Вот код для удаления не алфавитно-цифровых символов:
UPDATE {table} SET {column} = REGEXP_REPLACE({column}, '[^0-9a-zA-Z ]', '')
Ответ 15
Я попробовал несколько решений, но в конце использовал 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,' ',''),'-',''),'.',''),',',''),':',''),'?',''),'/',''),'!',''),'&',''),'@','')
Ответ 16
если вы используете 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>";
}
Ответ 17
функция alphanum (self отвечает) имеет ошибку, но я не знаю почему.
Для текста "cas synt ls 75W140 1L" return "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)
Ответ 18
Возможно, глупое предложение по сравнению с другими:
if(!preg_match("/^[a-zA-Z0-9]$/",$string)){
$sortedString=preg_replace("/^[a-zA-Z0-9]+$/","",$string);
}
