programing

MySQL이 여러 문자를 대체할 수 있습니까?

sourcejob 2022. 11. 5. 17:33
반응형

MySQL이 여러 문자를 대체할 수 있습니까?

MySQL 필드의 여러 문자를 바꾸려고 합니다.REPLACE 함수는 알고 있지만 한 번에 하나의 문자열만 대체됩니다.매뉴얼에 적절한 기능이 없습니다.

여러 문자열을 동시에 바꾸거나 삭제할 수 있습니까?예를 들어 공백은 대시로 대체하고 다른 구두점은 삭제해야 합니다.

치환 기능을 체인으로 할 수 있습니다.

select replace(replace('hello world','world','earth'),'hello','hi')

이것은 인쇄됩니다.hi earth.

서브쿼리를 사용하여 여러 문자열을 바꿀 수도 있습니다.

select replace(london_english,'hello','hi') as warwickshire_english
from (
    select replace('hello world','world','earth') as london_english
) sub

또는 JOIN을 사용하여 바꿉니다.

select group_concat(newword separator ' ')
from (
    select 'hello' as oldword
    union all
    select 'world'
) orig
inner join (
    select 'hello' as oldword, 'hi' as newword
    union all
    select 'world', 'earth'
) trans on orig.oldword = trans.oldword

독자의 연습으로서 일반적인 표 표현을 사용한 번역을 종료합니다.

캐스케이딩은 다중 문자 치환을 위한 mysql의 유일한 단순하고 간단한 솔루션입니다.

UPDATE table1 
SET column1 = replace(replace(REPLACE(column1, '\r\n', ''), '<br />',''), '<\r>','')

REPLACE 는 문자열 내의 모든 문자 또는 구문을 간단하게 치환할 수 있습니다.단, 구두점을 클렌징할 때는 예를 들어 단어 중간이나 마침표 뒤에 공백이나 문자가 있는 경우 등 패턴을 찾아야 합니다.그렇다면 정규 표현 치환 기능이 훨씬 더 강력할 것입니다.

업데이트: MySQL 버전 8+를 사용하는 경우 함수가 제공되며 다음과 같이 호출할 수 있습니다.

SELECT txt,
       REGEXP_REPLACE(REPLACE(txt, ' ', '-'),
                      '[^a-zA-Z0-9-]+',
                      '') AS `reg_replaced`
FROM test;

이 DB Fielle 온라인 데모를 참조하십시오.

이전 답변 - MySQL 버전 8 이전 버전을 사용하는 경우에만 계속 읽어보십시오.

나쁜 소식은 MySQL은 이러한 기능을 제공하지 않지만, 좋은 소식은 해결 방법을 제공할 수 있다는 것입니다. 이 블로그 게시물을 참조하십시오.

여러 문자열을 동시에 바꾸거나 삭제할 수 있습니까?예를 들어 공백은 대시로 대체하고 다른 구두점은 삭제해야 합니다.

위의 내용은 정규 표현 리페이서와 표준을 조합하여 달성할 수 있습니다.REPLACE기능.이 온라인 렉스테스터 데모에서 동작하고 있는 것을 확인할 수 있습니다.

SQL(간단함을 위한 함수 코드 제외):

SELECT txt,
       reg_replace(REPLACE(txt, ' ', '-'),
                   '[^a-zA-Z0-9-]+',
                   '',
                   TRUE,
                   0,
                   0
                   ) AS `reg_replaced`
FROM test;

이를 위해 lib_mysqludf_preg를 사용하고 있습니다.이것에 의해, 다음의 일이 가능하게 됩니다.

MySQL에서 직접 PCRE 정규 표현식 사용

이 라이브러리를 설치하면 다음과 같은 작업을 수행할 수 있습니다.

SELECT preg_replace('/(\\.|com|www)/','','www.example.com');

그러면 다음과 같은 이점을 얻을 수 있습니다.

example

CREATE FUNCTION IF NOT EXISTS num_as_word (name TEXT) RETURNS TEXT RETURN
(
    SELECT 
        REPLACE(
            REPLACE(
                REPLACE(
                    REPLACE(
                        REPLACE(
                            REPLACE(                            
                                REPLACE(
                                    REPLACE(                            
                                        REPLACE(IFNULL(name, ''), 
                                            '1', 'one'), 
                                            '2', 'two'), 
                                            '3', 'three'), 
                                            '4', 'four'), 
                                            '5', 'five'), 
                                            '6', 'six'), 
                                            '7', 'seven'),                                                          
                                            '8', 'eight'),
                                            '9', 'nine')
);

php에서

$dataToReplace = [1 => 'one', 2 => 'two', 3 => 'three'];
$sqlReplace = '';
foreach ($dataToReplace as $key => $val) {
    $sqlReplace = 'REPLACE(' . ($sqlReplace ? $sqlReplace : 'replace_field') . ', "' . $key . '", "' . $val . '")';
}
echo $sqlReplace;

결과

REPLACE(
    REPLACE(
        REPLACE(replace_field, "1", "one"),
    "2", "two"),
"3", "three");

UPDATE schools SET
slug = lower(name),
slug = REPLACE(slug, '|', ' '),
slug = replace(slug, '.', ' '),
slug = replace(slug, '"', ' '),
slug = replace(slug, '@', ' '),
slug = replace(slug, ',', ' '),
slug = replace(slug, '\'', ''),
slug = trim(slug),
slug = replace(slug, ' ', '-'),
slug = replace(slug, '--', '-');

UPDATE 스쿨 SET 슬러그 = replace(교체, '--', '-');

MySQL Version 8+ 를 사용하고 있는 경우는, 이하에, 한층 더 도움이 되는 빌트인 기능이 있습니다.

스트링 교체하다 산출량
w"w\'w. ex%a&m:p l–e.c)o(m "'%&:)(– www.example.com

MySQL 쿼리:

SELECT REGEXP_REPLACE('`w"w\'w.    ex%a&m:p     l–e.c)o(m`', '[("\'%[:blank:]&:–)]', '');

거의 모든 도청 캐릭터들이...

SELECT REGEXP_REPLACE(column, '[\("\'%[[:blank:]]&:–,#$@!;\\[\\]\)<>\?\*\^]+','')

실제 시나리오.

demo에 저장된 파일명을 모두 특수문자로 업데이트해야 했습니다.

SELECT * FROM demo;

| uri                                                                          |
|------------------------------------------------------------------------------|
| private://webform/applicant_details/129/offers   upload  winners .png        |
| private://webform/applicant_details/129/student : class & teacher data.pdf   |
| private://webform/applicant_details/130/tax---user's---data__upload.pdf      |
| private://webform/applicant_details/130/Applicant Details _ report_0_2.pdf   |
| private://webform/applicant_details/131/india&asia%population  huge.pdf      |

테스트 케이스 -

테이블에는 파일 이름에 특수 문자가 포함된 여러 행이 있습니다.

조언:

파일 이름에서 모든 특수 문자를 제거하고 파일 이름이 작은 a-z, A-Z, 0-9, 도트 및 밑줄을 사용합니다.

예상되는 결과는 다음과 같습니다.

| uri                                                                          |
|------------------------------------------------------------------------------|
| private://webform/applicant_details/129/offers_upload_winners_.png           |
| private://webform/applicant_details/129/student_class_teacher_data.pdf       |
| private://webform/applicant_details/130/tax_user_s_data_upload.pdf           |
| private://webform/applicant_details/130/applicant_details_report_0_2.pdf     |
| private://webform/applicant_details/131/india_asia_population_huge.pdf       |

좋아, 차근차근 계획을 세웁시다.

1st  - let's find the file name
2nd  - run all the find replace on that file name part only
3rd  - replace the new file name with an old one

어떻게 해야 되지?

더 잘 이해할 수 있도록 전체 동작을 한 덩어리로 나누자.

다음 함수는 풀 경로에서만 파일 이름을 추출합니다(예: "Applicant Details _ report_0_2.pdf").

SELECT                      -- MySQL SELECT statement
  SUBSTRING_INDEX           -- MySQL built-in function
  (                         -- Function start Parentheses
    uri,                    -- my table column
    '/',                    -- delimiter (the last / in full path; left to right ->)
    -1                      -- start from the last and find the 1st one (from right to left <-)
    )                       -- Function end Parentheses
from                        -- MySQL FROM statement
  demo;                     -- My table name

#1 조회 결과

| uri                                |
|------------------------------------|
| offers   upload  winners .png      |
| student : class & teacher data.pdf |
| tax---user's---data__upload.pdf    |
| Applicant Details _ report_0_2.pdf |
| india&asia%population  huge.pdf    |

이제 생성된 파일 이름 결과 내에서 검색 및 치환해야 합니다.

SELECT 
  REGEXP_REPLACE(                       -- MySQL REGEXP_REPLACE built-in function   (string, pattern, replace)
    SUBSTRING_INDEX(uri, '/', -1),      -- File name only
    '[^a-zA-Z0-9_.]+',                  -- Find everything which is not a-z, A-Z, 0-9, . or _.
    '_'                                 -- Replace with _
  ) AS uri                              -- Give a alias column name for whole result
from 
  demo;

#2 조회 결과

| uri                                |
|------------------------------------|
| offers_upload_winners_.png         |
| student_class_teacher_data.pdf     |
| tax_user_s_data__upload.pdf        |
| Applicant_Details___report_0_2.pdf |
| india_asia_population_huge.pdf     |

참고 - 패턴의 마지막 '+'는 ---- 또는 여러 개의 공백 '과 같은 반복적인 단어에 대한 것입니다. 아래 정규식 패턴에서 '+'가 없는 결과에 주목하십시오.

SELECT 
  REGEXP_REPLACE(                       -- MySQL REGEXP_REPLACE built-in function   (string, pattern, replace)
    SUBSTRING_INDEX(uri, '/', -1),      -- File name only
    '[^a-zA-Z0-9_.]',                   -- Find everything which is not a-z, A-Z, 0-9, . or _.
    '_'                                 -- Replace with _
  ) AS uri                              -- Give a alias column name for whole result
from 
  demo;

#3 조회 결과

| uri                                |
|------------------------------------|
| offers___upload__winners_.png      |
| student___class___teacher_data.pdf |
| tax___user_s___data__upload.pdf    |
| Applicant_Details___report_0_2.pdf |
| india_asia_population__huge.pdf    |

이제 특수문자(. 및 _ 허용)가 없는 파일 이름이 있습니다.그러나 문제는 파일 이름에 대문자와 여러 개의 밑줄이 있다는 것입니다.

먼저 파일 이름을 낮춥니다.

SELECT 
  LOWER(
    REGEXP_REPLACE(
      SUBSTRING_INDEX(uri, '/', -1), 
      '[^a-zA-Z0-9_.]', 
      '_'
    )
  ) AS uri 
from 
  demo;

#4 조회 결과

| uri                                |
|------------------------------------|
| offers_upload_winners_.png         |
| student_class_teacher_data.pdf     |
| tax_user_s_data__upload.pdf        |
| applicant_details___report_0_2.pdf |
| india_asia_population_huge.pdf     |

이제 모든 것이 소문자로 표시되지만 밑줄은 그대로입니다.그래서 저희가 REGEX를 다 싸서REGEX를 하나 더...

SELECT 
  LOWER(
    REGEXP_REPLACE(                     -- this wrapper will solve the multiple underscores issue
      REGEXP_REPLACE(
        SUBSTRING_INDEX(uri, '/', -1), 
        '[^a-zA-Z0-9_.]+', 
        '_'
      ), 
      '[_]+',                           -- if 1st regex action has multiple __ then find it
      '_'                               -- and replace them with single _
    )
  ) AS uri 
from 
  demo;

#5 조회 결과

| uri                              |
|----------------------------------|
| offers_upload_winners_.png       |
| student_class_teacher_data.pdf   |
| tax_user_s_data_upload.pdf       |
| applicant_details_report_0_2.pdf |
| india_asia_population_huge.pdf   |

축하합니다! 우리가 찾던 것을 찾았습니다.UPDATE TIME! 예!!

UPDATE                                      -- run a MySQL UPDATE statement
  demo                                      -- tell MySQL to which table you want to update
SET                                         -- put SET statement to set the updated values in desire column 
  uri = REPLACE(                            -- tell MySQL to which column you want to update,
                                            -- I am also putting REPLACE function to replace existing values with new one
                                            -- REPLACE (string, replace, with-this)
    uri,                                    -- my column to replace 
    SUBSTRING_INDEX(uri, '/', -1),          -- my file name part "Applicant Details _ report_0_2.pdf"
                                            -- without doing any action
    LOWER(                                  -- "applicant_details_report_0_2.pdf"
      REGEXP_REPLACE(                       -- "Applicant_Details_report_0_2.pdf"
        REGEXP_REPLACE(                     -- "Applicant_Details___report_0_2.pdf"
          SUBSTRING_INDEX(uri, '/', -1),    -- "Applicant Details _ report_0_2.pdf"
          '[^a-zA-Z0-9_.]+', 
          '_'
        ), 
        '[_]+', 
        '_'
      )
    )
  );

그리고 UPDATE Query 이후 결과는 다음과 같습니다.

| uri                                                                      |
|--------------------------------------------------------------------------|
| private://webform/applicant_details/152/offers_upload_winners_.png       |
| private://webform/applicant_details/153/student_class_teacher_data.pdf   |
| private://webform/applicant_details/153/tax_user_s_data_upload.pdf       |
| private://webform/applicant_details/154/applicant_details_report_0_2.pdf |
| private://webform/applicant_details/154/india_asia_population_huge.pdf   |

샘플 데이터 스크립트

DROP TABLE IF EXISTS `demo`;
CREATE TABLE `demo` (
  `uri` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT 'The S3 URI of the file.',
  `filesize` bigint unsigned NOT NULL DEFAULT '0' COMMENT 'The size of the file in bytes.',
  `timestamp` int unsigned NOT NULL DEFAULT '0' COMMENT 'UNIX timestamp for when the file was added.',
  `dir` int NOT NULL DEFAULT '0' COMMENT 'Boolean indicating whether or not this object is a directory.',
  `version` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8_bin DEFAULT '' COMMENT 'The S3 VersionId of the object.'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `demo` (`uri`, `filesize`, `timestamp`, `dir`, `version`) VALUES
('private://webform/applicant_details/152/offers   upload  winners .png', 14976905, 1658397516, 0, ''),
('private://webform/applicant_details/153/student : class & teacher data.pdf', 0, 1659525447, 1, ''),
('private://webform/applicant_details/153/tax---user\'s---data__upload.pdf', 98449, 1658397516, 0, ''),
('private://webform/applicant_details/154/Applicant Details _ report_0_2.pdf', 0, 1659525447, 1, ''),
('private://webform/applicant_details/154/india&asia%population  huge.pdf', 13301, 1658397517, 0, '');

대단히 감사합니다.

MySQL: SELECT, UPDATE, REPLACE, SUBSTRING_INDEX, LOWER, REGEXP_REplace

MySQL 쿼리 포맷터:Code Beautify가 이렇게 멋진 도구를 만들어 준 덕분입니다.

언급URL : https://stackoverflow.com/questions/1671040/can-mysql-replace-multiple-characters

반응형

'programing' 카테고리의 다른 글

JavaScript를 사용하여 '터치 스크린' 장치를 감지하는 가장 좋은 방법은 무엇입니까?  (0) 2022.11.05
지속적인 편집으로 주문 데이터 무결성 유지  (0) 2022.11.05
케이스/스위치 스테이트먼트에 대응하는 Python은 무엇입니까?  (0) 2022.11.05
MySQL 함수는 두 날짜 사이의 근무 일수를 구합니다.  (0) 2022.11.05
mysqldump 출력을 작은 파일로 분할하려면 어떻게 해야 합니까?  (0) 2022.11.05

'programing'의 다른글

  • 현재글MySQL이 여러 문자를 대체할 수 있습니까?

관련글

댓글

티스토리툴바