MySQL에서 정규 표현식을 바꾸는 방법은 무엇입니까?
~ 500k 행의 테이블이 있습니다. varchar (255) UTF8 열에 filename는 파일 이름이 있습니다.
파일 이름에서 다양한 이상한 문자를 제거하려고합니다. 문자 클래스를 사용한다고 생각했습니다. [^a-zA-Z0-9()_ .\-]
이제 MySQL에 정규식을 통해 바꿀 수있는 함수가 있습니까? REPLACE () 함수와 유사한 기능을 찾고 있습니다. 간단한 예는 다음과 같습니다.
SELECT REPLACE('stackowerflow', 'ower', 'over');
Output: "stackoverflow"
/* does something like this exist? */
SELECT X_REG_REPLACE('Stackoverflow','/[A-Zf]/','-');
Output: "-tackover-low"
내가 알고 정규 표현식 / RLIKE , 그러나 그 만 확인 하면 일치가 아니라 어떤 경기입니다.
(나는 할 수 는 "할 SELECT pkey_id,filename FROM foo WHERE filename RLIKE '[^a-zA-Z0-9()_ .\-]'"PHP 스크립트에서하는을 preg_replace다음과 "을 UPDATE foo ... WHERE pkey_id=..."하지만 같은 그 모습 느린 & 추한 해킹을 마지막으로 리조트)
MySQL 8.0 이상에서는 기본적으로 사용할 수 있습니다 REGEXP_REPLACE.
REGEXP_REPLACE (expr, pat, repl [, pos [, 발생 [, match_type]]])
패턴 pat에 의해 지정된 정규 표현식과 일치하는 문자열 expr에서 발생하는 문자열을 대체 문자열 repl로 바꾸고 결과 문자열을 리턴합니다. expr, pat 또는 repl이 NULL이면 반환 값은 NULL입니다.
그리고 정규 표현식 지원 :
이전에는 MySQL이 Henry Spencer 정규식 라이브러리를 사용하여 정규식 연산자 (REGEXP, RLIKE)를 지원했습니다.
ICU (International Components for Unicode)를 사용하여 정규식 지원이 다시 구현되어 완전한 유니 코드 지원을 제공하며 멀티 바이트 안전합니다. REGEXP_LIKE () 함수는 REGEXP 및 RLIKE 연산자의 방식으로 정규 표현식 일치를 수행하며, 이제 해당 함수의 동의어입니다. 또한 REGEXP_INSTR (), REGEXP_REPLACE () 및 REGEXP_SUBSTR () 함수를 사용하여 각각 일치 위치를 찾고 하위 문자열 대체 및 추출을 수행 할 수 있습니다.
SELECT REGEXP_REPLACE('Stackoverflow','[A-Zf]','-',1,0,'c');
-- Output:
-tackover-low
아니.
그러나 서버에 액세스 할 수 있으면 mysql-udf-regexp 와 같은 사용자 정의 함수 (UDF)를 사용할 수 있습니다 .
편집 : MySQL 8.0 이상은 기본적으로 REGEXP_REPLACE를 사용할 수 있습니다. 위의 답변에서 더 많은
대신 MariaDB를 사용하십시오. 기능이 있습니다
REGEXP_REPLACE(col, regexp, replace)
참조 MariaDB의 문서 및 PCRE 정규 표현식 향상
정규식 그룹화도 사용할 수 있습니다 (매우 유용하다는 것을 알았습니다).
SELECT REGEXP_REPLACE("stackoverflow", "(stack)(over)(flow)", '\\2 - \\1 - \\3')
보고
over - stack - flow
이것을 작동시키는 내 무차별 대입 방법은 다음과 같습니다.
- 테이블을 버리고-
mysqldump -u user -p database table > dump.sql - 몇 가지 패턴 찾기 및 바꾸기-
find /path/to/dump.sql -type f -exec sed -i 's/old_string/new_string/g' {} \;, 파일에서 수행 할 수있는 다른 perl regeular 표현식이 있습니다. - 테이블 가져 오기-
mysqlimport -u user -p database table < dump.sql
문자열이 데이터 세트의 다른 곳에 있지 않게하려면 몇 가지 정규식을 실행하여 모두 비슷한 환경에서 발생하는지 확인하십시오. 또한 심도있는 정보를 잃어버린 것을 실수로 파괴 할 경우를 대비하여 교체를 실행하기 전에 백업을 만드는 것이 어렵지 않습니다.
나는 최근에 정규 표현식을 사용하여 문자열을 대체하는 MySQL 함수를 작성했습니다. 다음 위치에서 내 게시물을 찾을 수 있습니다.
http://techras.wordpress.com/2011/06/02/regex-replace-for-mysql/
함수 코드는 다음과 같습니다.
DELIMITER $$
CREATE FUNCTION `regex_replace`(pattern VARCHAR(1000),replacement VARCHAR(1000),original VARCHAR(1000))
RETURNS VARCHAR(1000)
DETERMINISTIC
BEGIN
DECLARE temp VARCHAR(1000);
DECLARE ch VARCHAR(1);
DECLARE i INT;
SET i = 1;
SET temp = '';
IF original REGEXP pattern THEN
loop_label: LOOP
IF i>CHAR_LENGTH(original) THEN
LEAVE loop_label;
END IF;
SET ch = SUBSTRING(original,i,1);
IF NOT ch REGEXP pattern THEN
SET temp = CONCAT(temp,ch);
ELSE
SET temp = CONCAT(temp,replacement);
END IF;
SET i=i+1;
END LOOP;
ELSE
SET temp = original;
END IF;
RETURN temp;
END$$
DELIMITER ;
실행 예 :
mysql> select regex_replace('[^a-zA-Z0-9\-]','','2my test3_text-to. check \\ my- sql (regular) ,expressions ._,');
우리는 정규 표현식을 사용하지 않고이 문제를 해결합니다.이 쿼리는 정확히 일치하는 문자열 만 바꿉니다.
update employee set
employee_firstname =
trim(REPLACE(concat(" ",employee_firstname," "),' jay ',' abc '))
예:
emp_id employee_firstname
제이 1
2 어이 어제이
3 어치
쿼리 결과를 실행 한 후 :
emp_id employee_firstname
1 ABC
2 abc ajay
3 ABC
이 질문이 제기 된 후 만족스러운 답변이 있음을 알려드립니다. 이 훌륭한 패키지를 살펴보십시오.
https://github.com/mysqludf/lib_mysqludf_preg
샘플 SQL :
SELECT PREG_REPLACE('/(.*?)(fox)/' , 'dog' , 'the quick brown fox' ) AS demo;
이 질문에 링크 된 이 블로그 게시물 에서 패키지를 찾았습니다 .
업데이트 2 : REGEXP_REPLACE를 포함한 유용한 정규 표현식 함수 가 MySQL 8.0에서 제공되었습니다. 이전 버전을 사용하도록 제한되어 있지 않으면 불필요하게 읽을 수 있습니다.
업데이트 1 : 이제 이것을 블로그 게시물로 만들었습니다 : http://stevettt.blogspot.co.uk/2018/02/a-mysql-regular-expression-replace.html
다음 은 Rasika Godawatte가 제공 하는 기능을 확장 하지만 단일 문자를 테스트하는 대신 필요한 모든 하위 문자열을 탐색 합니다.
-- ------------------------------------------------------------------------------------
-- USAGE
-- ------------------------------------------------------------------------------------
-- SELECT reg_replace(<subject>,
-- <pattern>,
-- <replacement>,
-- <greedy>,
-- <minMatchLen>,
-- <maxMatchLen>);
-- where:
-- <subject> is the string to look in for doing the replacements
-- <pattern> is the regular expression to match against
-- <replacement> is the replacement string
-- <greedy> is TRUE for greedy matching or FALSE for non-greedy matching
-- <minMatchLen> specifies the minimum match length
-- <maxMatchLen> specifies the maximum match length
-- (minMatchLen and maxMatchLen are used to improve efficiency but are
-- optional and can be set to 0 or NULL if not known/required)
-- Example:
-- SELECT reg_replace(txt, '^[Tt][^ ]* ', 'a', TRUE, 2, 0) FROM tbl;
DROP FUNCTION IF EXISTS reg_replace;
DELIMITER //
CREATE FUNCTION reg_replace(subject VARCHAR(21845), pattern VARCHAR(21845),
replacement VARCHAR(21845), greedy BOOLEAN, minMatchLen INT, maxMatchLen INT)
RETURNS VARCHAR(21845) DETERMINISTIC BEGIN
DECLARE result, subStr, usePattern VARCHAR(21845);
DECLARE startPos, prevStartPos, startInc, len, lenInc INT;
IF subject REGEXP pattern THEN
SET result = '';
-- Sanitize input parameter values
SET minMatchLen = IF(minMatchLen < 1, 1, minMatchLen);
SET maxMatchLen = IF(maxMatchLen < 1 OR maxMatchLen > CHAR_LENGTH(subject),
CHAR_LENGTH(subject), maxMatchLen);
-- Set the pattern to use to match an entire string rather than part of a string
SET usePattern = IF (LEFT(pattern, 1) = '^', pattern, CONCAT('^', pattern));
SET usePattern = IF (RIGHT(pattern, 1) = '$', usePattern, CONCAT(usePattern, '$'));
-- Set start position to 1 if pattern starts with ^ or doesn't end with $.
IF LEFT(pattern, 1) = '^' OR RIGHT(pattern, 1) <> '$' THEN
SET startPos = 1, startInc = 1;
-- Otherwise (i.e. pattern ends with $ but doesn't start with ^): Set start pos
-- to the min or max match length from the end (depending on "greedy" flag).
ELSEIF greedy THEN
SET startPos = CHAR_LENGTH(subject) - maxMatchLen + 1, startInc = 1;
ELSE
SET startPos = CHAR_LENGTH(subject) - minMatchLen + 1, startInc = -1;
END IF;
WHILE startPos >= 1 AND startPos <= CHAR_LENGTH(subject)
AND startPos + minMatchLen - 1 <= CHAR_LENGTH(subject)
AND !(LEFT(pattern, 1) = '^' AND startPos <> 1)
AND !(RIGHT(pattern, 1) = '$'
AND startPos + maxMatchLen - 1 < CHAR_LENGTH(subject)) DO
-- Set start length to maximum if matching greedily or pattern ends with $.
-- Otherwise set starting length to the minimum match length.
IF greedy OR RIGHT(pattern, 1) = '$' THEN
SET len = LEAST(CHAR_LENGTH(subject) - startPos + 1, maxMatchLen), lenInc = -1;
ELSE
SET len = minMatchLen, lenInc = 1;
END IF;
SET prevStartPos = startPos;
lenLoop: WHILE len >= 1 AND len <= maxMatchLen
AND startPos + len - 1 <= CHAR_LENGTH(subject)
AND !(RIGHT(pattern, 1) = '$'
AND startPos + len - 1 <> CHAR_LENGTH(subject)) DO
SET subStr = SUBSTRING(subject, startPos, len);
IF subStr REGEXP usePattern THEN
SET result = IF(startInc = 1,
CONCAT(result, replacement), CONCAT(replacement, result));
SET startPos = startPos + startInc * len;
LEAVE lenLoop;
END IF;
SET len = len + lenInc;
END WHILE;
IF (startPos = prevStartPos) THEN
SET result = IF(startInc = 1, CONCAT(result, SUBSTRING(subject, startPos, 1)),
CONCAT(SUBSTRING(subject, startPos, 1), result));
SET startPos = startPos + startInc;
END IF;
END WHILE;
IF startInc = 1 AND startPos <= CHAR_LENGTH(subject) THEN
SET result = CONCAT(result, RIGHT(subject, CHAR_LENGTH(subject) + 1 - startPos));
ELSEIF startInc = -1 AND startPos >= 1 THEN
SET result = CONCAT(LEFT(subject, startPos), result);
END IF;
ELSE
SET result = subject;
END IF;
RETURN result;
END//
DELIMITER ;
데모
한계
- 물론이 방법은 제목 문자열이 클 때 시간이 걸립니다. 업데이트 : 최소 및 최대 일치 길이 매개 변수를 추가하여 효율성을 높이도록했습니다 (제로 = 알 수 없음 / 제한 없음).
- 이 되지 역 참조 (예 치환 수
\1,\2촬상기를 대체 등). 이 기능이 필요한 경우 각 응답 에서 보조 찾기 및 바꾸기를 허용하도록 기능을 업데이트하여 해결 방법을 제공하는 이 답변 을 참조하십시오 (복잡성이 증가함에 따라). - 경우
^및 / 또는$등이 같은 예를 들어, 패턴 - 패턴에 사용되는, 그들은 매우 시작하고 매우 각각 종료에 있어야(^start|end$)지원되지 않습니다. - 전체 매칭이 욕심인지 아닌지 욕설인지를 지정하는 "greedy"플래그가 있습니다. 단일 정규식 (예 :) 내에서 욕심과 게으른 일치를 결합하는
a.*?b.*것은 지원되지 않습니다.
사용 예
이 함수는 다음 StackOverflow 질문에 답변하는 데 사용되었습니다.
- MySQL / 정규 표현식 대체 기에서 단어를 계산하는 방법은 무엇입니까?
- MySQL 문자열에서 n 번째 단어를 추출하고 단어 발생 횟수를 계산하는 방법은 무엇입니까?
- MySQL의 텍스트 필드에서 두 개의 연속 숫자를 추출하는 방법은 무엇입니까?
- MySQL의 문자열에서 알파벳이 아닌 숫자를 모두 제거하는 방법은 무엇입니까?
- MySQL 문자열에서 특정 문자의 다른 모든 인스턴스를 바꾸는 방법은 무엇입니까?
- MySQL 테이블의 여러 열에서 지정된 최소 길이의 고유 단어를 모두 얻는 방법은 무엇입니까?
당신은 그것을 할 수 있습니다 ...하지만 현명하지는 않습니다 ... 이것은 내가 시도 할만 큼 대담합니다 ... 정규 RegEx가 perl 등을 사용하여 훨씬 더 나은 것을 지원하는 한.
UPDATE db.tbl
SET column =
CASE
WHEN column REGEXP '[[:<:]]WORD_TO_REPLACE[[:>:]]'
THEN REPLACE(column,'WORD_TO_REPLACE','REPLACEMENT')
END
WHERE column REGEXP '[[:<:]]WORD_TO_REPLACE[[:>:]]'
다음과 같이 SELECT 쿼리에서 IF 조건을 사용할 수 있습니다.
"ABC", "ABC1", "ABC2", "ABC3", ...가있는 모든 항목에 대해 "ABC"로 바꾸고 SELECT 쿼리에서 REGEXP 및 IF () 조건을 사용하려는 경우이를 달성 할 수 있다고 가정합니다. .
통사론:
SELECT IF(column_name REGEXP 'ABC[0-9]$','ABC',column_name)
FROM table1
WHERE column_name LIKE 'ABC%';
예:
SELECT IF('ABC1' REGEXP 'ABC[0-9]$','ABC','ABC1');
아래의 것은 기본적으로 왼쪽에서 첫 번째 일치를 찾은 다음 모든 발생을 대체합니다 ( mysql-5.6 에서 테스트 됨 ).
용법:
SELECT REGEX_REPLACE('dis ambiguity', 'dis[[:space:]]*ambiguity', 'disambiguity');
이행:
DELIMITER $$
CREATE FUNCTION REGEX_REPLACE(
var_original VARCHAR(1000),
var_pattern VARCHAR(1000),
var_replacement VARCHAR(1000)
) RETURNS
VARCHAR(1000)
COMMENT 'Based on https://techras.wordpress.com/2011/06/02/regex-replace-for-mysql/'
BEGIN
DECLARE var_replaced VARCHAR(1000) DEFAULT var_original;
DECLARE var_leftmost_match VARCHAR(1000) DEFAULT
REGEX_CAPTURE_LEFTMOST(var_original, var_pattern);
WHILE var_leftmost_match IS NOT NULL DO
IF var_replacement <> var_leftmost_match THEN
SET var_replaced = REPLACE(var_replaced, var_leftmost_match, var_replacement);
SET var_leftmost_match = REGEX_CAPTURE_LEFTMOST(var_replaced, var_pattern);
ELSE
SET var_leftmost_match = NULL;
END IF;
END WHILE;
RETURN var_replaced;
END $$
DELIMITER ;
DELIMITER $$
CREATE FUNCTION REGEX_CAPTURE_LEFTMOST(
var_original VARCHAR(1000),
var_pattern VARCHAR(1000)
) RETURNS
VARCHAR(1000)
COMMENT '
Captures the leftmost substring that matches the [var_pattern]
IN [var_original], OR NULL if no match.
'
BEGIN
DECLARE var_temp_l VARCHAR(1000);
DECLARE var_temp_r VARCHAR(1000);
DECLARE var_left_trim_index INT;
DECLARE var_right_trim_index INT;
SET var_left_trim_index = 1;
SET var_right_trim_index = 1;
SET var_temp_l = '';
SET var_temp_r = '';
WHILE (CHAR_LENGTH(var_original) >= var_left_trim_index) DO
SET var_temp_l = LEFT(var_original, var_left_trim_index);
IF var_temp_l REGEXP var_pattern THEN
WHILE (CHAR_LENGTH(var_temp_l) >= var_right_trim_index) DO
SET var_temp_r = RIGHT(var_temp_l, var_right_trim_index);
IF var_temp_r REGEXP var_pattern THEN
RETURN var_temp_r;
END IF;
SET var_right_trim_index = var_right_trim_index + 1;
END WHILE;
END IF;
SET var_left_trim_index = var_left_trim_index + 1;
END WHILE;
RETURN NULL;
END $$
DELIMITER ;
나는 이것을 달성하는 쉬운 방법이 있다고 생각하며 그것은 나에게 잘 작동한다.
REGEX를 사용하여 행을 선택하려면
SELECT * FROM `table_name` WHERE `column_name_to_find` REGEXP 'string-to-find'
REGEX를 사용하여 행을 업데이트하려면
UPDATE `table_name` SET column_name_to_find=REGEXP_REPLACE(column_name_to_find, 'string-to-find', 'string-to-replace') WHERE column_name_to_find REGEXP 'string-to-find'
REGEXP 참조 : https://www.geeksforgeeks.org/mysql-regular-expressions-regexp/
참고 URL : https://stackoverflow.com/questions/986826/how-to-do-a-regular-expression-replace-in-mysql
'development' 카테고리의 다른 글
| LINQ를 사용하여 하나의 List <>에서 다른 List <>에없는 항목을 가져옵니다. (0) | 2020.02.14 |
|---|---|
| 개체가 비어 있습니까? (0) | 2020.02.14 |
| 부호없는 int와 size_t (0) | 2020.02.14 |
| 파이썬 제너레이터와 이터레이터의 차이점 (0) | 2020.02.14 |
| WCF 대 ASP.NET 웹 API (0) | 2020.02.14 |