MySQL 문자열에서 영숫자가 아닌 모든 문자를 제거하려면 어떻게 해야 합니까?
문자열을 비교하는 루틴을 만들고 있지만 효율성을 높이기 위해 문자나 숫자가 아닌 문자를 모두 제거해야 합니다.
는 여러 개를 REPLACE지금은 기능하지만, 더 빠르고 좋은 해결책이 있을까요?
MySQL 8.0 이후 사용
마이클의 호의로요아래 jakubeczy의 답변은 Regex로 대체되어 MySQL에서 지원됩니다.
UPDATE {table} SET {column} = REGEXP_REPLACE({column}, '[^0-9a-zA-Z ]', '')
MySQL 5.7 이하 사용
여기서는 Regex가 지원되지 않습니다.영숫자라고 하는 독자적인 함수를 작성해야 했습니다.이 기능을 사용하면 문자가 지워집니다.
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);
IF str IS NOT NULL THEN
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;
ELSE
SET ret='';
END IF;
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-regexphttpsgithub.com/hholzgra/
라틴 문자 및 키릴 문자를 위한 스트레이트 및 배틀 테스트 솔루션:
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-привет ""]')
가장 빨리 찾을 수 있는 방법은 convert()를 사용하는 것입니다.
(Doc.에서)USING을 사용하는 CONVERT()는 다른 문자 집합 간의 데이터 변환에 사용됩니다.
예:
convert(string USING ascii)
사용자의 경우 올바른 문자 집합이 자동으로 정의됩니다.
의사로부터의 메모.사용 폼CONVERT()는 4.1.0 이후부터 사용할 수 있습니다.
Ryan Shillington의 답변에 따라 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 ;
MySQL에서는 ' 또는 '와 같은 문자를 알파벳으로 간주하므로 주의하십시오.다음과 같은 것을 사용하는 것이 좋습니다.
c가 'a'와 'z' 사이이거나 c가 'A'와 'Z' 사이이거나 c가 '0'과 '9' 사이이거나 c = '-'인 경우
이 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 확장을 설치해야 합니다.
이것은 제가 다른 답변에 올리고 블로그에 올린 정규 표현 리페이서 함수로 할 수 있습니다.이것이 가장 효율적인 해결책은 아닐 수도 있고, 수중에 있는 일을 하기 위해 과잉 살상으로 보일 수도 있지만, 스위스 군용 나이프처럼, 다른 이유로 유용할 수도 있다.
이 렉스터 온라인 데모에서는 영숫자가 아닌 모든 문자를 삭제할 수 있습니다.
SQL(간단함을 위한 함수 코드 제외):
SELECT txt,
reg_replace(txt,
'[^a-zA-Z0-9]+',
'',
TRUE,
0,
0
) AS `reg_replaced`
FROM test;
데이터베이스에서 약간 다른 성을 대조하려고 할 때도 비슷한 문제가 있었습니다.예를 들어, 때때로 사람들은 "맥도널드"와 "맥도날드" 또는 "세인트 존"과 "세인트 존"으로 같은 사람의 이름을 입력했다.
Mysql 데이터를 변환하는 대신 문자열을 사용하여 알파벳 전용 정규 표현을 만드는 함수를 만들어 문제를 해결했습니다.
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
.
이는 알려진 데이터 집합에서만 권장됩니다. 그렇지 않으면 일부 특수 문자는 화이트리스트 접근 방식이 아닌 블랙리스트 접근 방식으로 슬쩍 지나치는 것이 일반적입니다.
물론 가장 간단한 방법은 내장된 강력한 화이트리스트가 없기 때문에(예: regex 치환을 통해) SQL 외부에서 데이터를 사전 검증하는 것입니다.
프로시저에서는 문자열의 알파벳 문자만 가져오면 됩니다.또, 다음의 조작을 실시했습니다.
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;
영숫자가 아닌 문자를 삭제하는 대신 영숫자가 아닌 문자를 대체해야 하므로 Ryan Shillington의 영숫자를 기반으로 작성했습니다.최대 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- |
+--------------+--------------------------+-------------------------------------+
필요한 경우 영숫자 함수를 별도로 추가해야 합니다. 예를 들어 여기에 있습니다.
몇 가지 방법을 시도해 보았지만, 결국엔 사용했어요.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,' ',''),'-',''),'.',''),',',''),':',''),'?',''),'/',''),'!',''),'&',''),'@','')
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>";
}
영숫자 함수(자체 응답)에 버그가 있는데 왜 그런지는 모르겠어요."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);
}
언급URL : https://stackoverflow.com/questions/6942973/how-to-remove-all-non-alpha-numeric-characters-from-a-string-in-mysql
'programing' 카테고리의 다른 글
| mysql LOAD DATA/mysqlimport 성능 향상? (0) | 2023.01.21 |
|---|---|
| 팬더 데이터 프레임 목록 연결 (0) | 2023.01.21 |
| PHP | define() vs. const. (0) | 2023.01.21 |
| 스크립트 언어(Perl, Python, Ruby 등)가 셸 언어로 적합하지 않은 이유는 무엇입니까? (0) | 2023.01.21 |
| 마리아답:WHERE 절에서 창 함수 LAG 결과 사용 (0) | 2023.01.15 |