MySQL의 문자열에서 알파벳이 아닌 모든 문자를 제거하는 방법은 무엇입니까?
문자열을 비교하는 루틴을 작업 중이지만 효율성을 높이려면 문자 나 숫자가 아닌 모든 문자를 제거해야합니다.
REPLACE현재 여러 기능을 사용 하고 있지만 더 빠르고 더 좋은 솔루션이 있습니까?
이 답변 중 어느 것도 나를 위해 일하지 않았습니다. 나는 문자를 제거하는 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)
만세!
성능 관점에서 (그리고 당신이 쓰는 것보다 더 많이 읽는다는 가정하에)
가장 좋은 방법은 제거 된 버전의 열을 미리 계산하고 저장하는 것입니다. 이렇게하면 변환 작업이 덜 수행됩니다.
그런 다음 새 열에 색인을 추가하고 데이터베이스가 작업을 수행하도록 할 수 있습니다.
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-привет ""]')
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 ;
내가 찾을 수있는 가장 빠른 방법은 convert ()입니다.
Doc. USING이있는 CONVERT ()는 서로 다른 문자 집합간에 데이터를 변환하는 데 사용됩니다.
예:
convert(string USING ascii)
귀하의 경우 올바른 문자 집합 이 자체 정의됩니다.
Doc. 사용 의 형태 CONVERT()의로 사용할 수 있습니다 4.1.0 .
'또는»와 같은 문자는 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 확장을 설치해야합니다.
이것은 내가 다른 답변에 게시하고 여기에 블로그에 올린 정규 표현식 대체 함수로 수행 할 수 있습니다 . 가능한 가장 효율적인 솔루션이 아닐 수 있으며 손에 들고있는 작업에 과도하게 보일 수 있지만 스위스 군용 칼처럼 다른 이유로 유용 할 수 있습니다.
이 Rextester 온라인 데모 에서 영숫자가 아닌 모든 문자를 제거하는 동작을 볼 수 있습니다 .
SQL (간결성을 위해 함수 코드 제외) :
SELECT txt,
reg_replace(txt,
'[^a-zA-Z0-9]+',
'',
TRUE,
0,
0
) AS `reg_replaced`
FROM test;
나는 약간 다른 우리 데이터베이스의 성을 일치시키는 데 비슷한 문제가있었습니다. 예를 들어, 때때로 사람들은 "McDonald"와 "Mc Donald"또는 "St John"및 "St. John"과 같은 사람의 이름을 입력했습니다.
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 외부에서 데이터를 사전 검증하는 것입니다 (예 : 정규식 교체를 통해).
프로 시저에서 문자열의 알파벳 문자 만 가져와야했고 다음을 수행했습니다.
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- |
+--------------+--------------------------+-------------------------------------+
원한다면 영숫자 함수를 별도로 추가해야합니다. 예제를 위해 여기에 있습니다.
MySQL 8.0부터 정규 표현식을 사용하여 문자열에서 영숫자가 아닌 문자를 제거 할 수 있습니다. REGEXP_REPLACE 메소드가 있습니다.
다음은 영숫자가 아닌 문자를 제거하는 코드입니다.
UPDATE {table} SET {column} = REGEXP_REPLACE({column}, '[^0-9a-zA-Z ]', '')
아마도 다른 사람들에 비해 어리석은 제안 일 것입니다.
if(!preg_match("/^[a-zA-Z0-9]$/",$string)){
$sortedString=preg_replace("/^[a-zA-Z0-9]+$/","",$string);
}
I tried a few solutions but at the end used replace. My data set is part numbers and I fairly know what to expect. But just for sanity, I used PHP to build the long query:
$dirty = array(' ', '-', '.', ',', ':', '?', '/', '!', '&', '@');
$query = 'part_no';
foreach ($dirty as $dirt) {
$query = "replace($query,'$dirt','')";
}
echo $query;
This outputs something I used to get a headache from:
replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(part_no,' ',''),'-',''),'.',''),',',''),':',''),'?',''),'/',''),'!',''),'&',''),'@','')
if you are using php then....
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>";
}
the alphanum function (self answered) have a bug, but I don't know why. For text "cas synt ls 75W140 1L" return "cassyntls75W1401", "L" from the end is missing some how.
Now I use
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
//
(found on google)
ReferenceURL : https://stackoverflow.com/questions/6942973/how-to-remove-all-non-alpha-numeric-characters-from-a-string-in-mysql
'development' 카테고리의 다른 글
| 워드 프레스 : 'the_content'필터에 등록 된 모든 기능을 가져 오는 방법 (0) | 2021.01.05 |
|---|---|
| Github Ahead / Behind Metrics의 의미 (0) | 2021.01.05 |
| DialogFragment.getDialog가 null을 반환합니다. (0) | 2021.01.05 |
| Eclipse가 SBT의 종속성을 인식하도록하는 방법 (0) | 2021.01.05 |
| Resque 대기열의 작업 수를 프로그래밍 방식으로 가져옵니다. (0) | 2021.01.05 |