값을 한 필드에서 두 필드로 분할
membername사용자의 성과 이름이 모두 포함되어 있습니다.의 필드로 할 수 ?memberfirst,memberlast무슨 일입니까?
모든 레코드는 "이름 성" 형식입니다(따옴표와 공백 없음).
안타깝게도 MySQL에는 분할 문자열 함수가 없습니다.단, 다음 문서에서 설명하는 것과 같은 사용자 정의 함수를 만들 수 있습니다.
- Federico Cargnelutti에 의한 MySQL 스플릿 문자열 함수
이 기능을 사용하면:
DELIMITER $$
CREATE FUNCTION SPLIT_STR(
x VARCHAR(255),
delim VARCHAR(12),
pos INT
)
RETURNS VARCHAR(255) DETERMINISTIC
BEGIN
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
delim, '');
END$$
DELIMITER ;
다음과 같이 쿼리를 작성할 수 있습니다.
SELECT SPLIT_STR(membername, ' ', 1) as memberfirst,
SPLIT_STR(membername, ' ', 2) as memberlast
FROM users;
사용자 정의 함수를 사용하지 않고 조회가 좀 더 상세하게 진행되어도 상관 없는 경우 다음을 수행할 수도 있습니다.
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(membername, ' ', 1), ' ', -1) as memberfirst,
SUBSTRING_INDEX(SUBSTRING_INDEX(membername, ' ', 2), ' ', -1) as memberlast
FROM users;
SELECT 변형(사용자 정의 함수를 만들지 않음):
SELECT IF(
LOCATE(' ', `membername`) > 0,
SUBSTRING(`membername`, 1, LOCATE(' ', `membername`) - 1),
`membername`
) AS memberfirst,
IF(
LOCATE(' ', `membername`) > 0,
SUBSTRING(`membername`, LOCATE(' ', `membername`) + 1),
NULL
) AS memberlast
FROM `user`;
이 어프로치에서는, 다음의 작업도 실시합니다.
- 공백 없는 membername 값: 문자열 전체를 memberfirst에 추가하고 memberlast를 NULL로 설정합니다.
- 여러 공백이 있는 membername 값: 첫 번째 공백 앞에 있는 모든 것을 memberfirst에 추가하고 나머지(추가 공백 포함)를 memberlast에 추가합니다.
UPDATE 버전은 다음과 같습니다.
UPDATE `user` SET
`memberfirst` = IF(
LOCATE(' ', `membername`) > 0,
SUBSTRING(`membername`, 1, LOCATE(' ', `membername`) - 1),
`membername`
),
`memberlast` = IF(
LOCATE(' ', `membername`) > 0,
SUBSTRING(`membername`, LOCATE(' ', `membername`) + 1),
NULL
);
기존 답변이 지나치게 복잡하거나 특정 질문에 대한 엄격한 답변이 아닌 것 같습니다.
제 생각에 간단한 대답은 다음과 같은 질문입니다.
SELECT
SUBSTRING_INDEX(`membername`, ' ', 1) AS `memberfirst`,
SUBSTRING_INDEX(`membername`, ' ', -1) AS `memberlast`
;
나는 이 특정한 상황에서 두 단어 이상의 이름을 다룰 필요가 없다고 생각한다.올바르게 분할하려면 분할이 매우 어렵거나 경우에 따라서는 불가능할 수 있습니다.
- 요한 세바스찬 바흐
- 요한 볼프강 폰 괴테
- 에드거 앨런 포
- 야콥 루드비히 펠릭스 멘델스존-바르톨디
- 페테피 산도르
- 비라그 벤델네 파르카스 마르짓
- 黒澤 明
적절하게 설계된 데이터베이스에서는 사람 이름을 부분과 전체로 저장해야 합니다.물론 이것이 항상 가능한 것은 아니다.
질의의 일부로 이 작업을 수행할 계획이라면 이 작업은 수행하지 마십시오. 정말 성능 저하입니다.퍼포먼스를 중시하지 않는 상황(필드를 분할하는 일회성 이행 작업 등)이 있을 수 있지만, 미키마우스 데이터베이스 이외의 목적으로 정기적으로 이행하는 경우에는 자원을 낭비하는 것입니다.
어떤 식으로든 열의 일부만 처리해야 하는 경우 DB 설계에 결함이 있습니다.홈 주소록이나 레시피 애플리케이션, 기타 무수한 소규모 데이터베이스에서는 정상적으로 동작할 수 있지만, 「실제」시스템에는 대응할 수 없습니다.
이름의 구성 요소를 별도의 열에 저장합니다.문자 검색으로 열을 분할하는 것보다 간단한 연결(전체 이름이 필요할 때)로 열을 결합하는 것이 거의 항상 훨씬 빠릅니다.
어떤 이유로 필드를 분할할 수 없는 경우 최소한 추가 열을 입력하고 삽입/업데이트 트리거를 사용하여 필드를 채웁니다.3NF는 아니지만 데이터의 일관성을 보장하고 쿼리 속도를 크게 높일 수 있습니다.또한 추가 열을 동시에 소문자로 구분하여 검색할 경우 색인화함으로써 케이스 문제를 처리할 필요가 없도록 할 수도 있습니다.
또한 열과 트리거를 추가할 수 없는 경우 확장성이 없다는 것을 알고(클라이언트용인 경우 클라이언트에 알립니다).
(a) 물론 이 쿼리를 사용하여 스키마를 수정하여 쿼리가 아닌 테이블 내의 다른 열에 이름이 배치되도록 하는 경우 유효한 사용이라고 생각합니다.하지만 거듭 말씀드리지만, 질문에서 그렇게 하는 것은 좋은 생각이 아닙니다.
이것을 사용하다
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX( `membername` , ' ', 2 ),' ',1) AS b,
SUBSTRING_INDEX(SUBSTRING_INDEX( `membername` , ' ', -1 ),' ',2) AS c FROM `users` WHERE `userid`='1'
MySQL에서는 이 옵션이 작동합니다.
SELECT Substring(nameandsurname, 1, Locate(' ', nameandsurname) - 1) AS
firstname,
Substring(nameandsurname, Locate(' ', nameandsurname) + 1) AS lastname
FROM emp
질문에 정확히 대답하지는 않았지만, 같은 문제에 직면하게 되었습니다.
UPDATE people_exit SET last_name = SUBSTRING_INDEX(fullname,' ',-1)
UPDATE people_exit SET middle_name = TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(fullname,last_name,1),' ',-2))
UPDATE people_exit SET middle_name = '' WHERE CHAR_LENGTH(middle_name)>3
UPDATE people_exit SET first_name = SUBSTRING_INDEX(fullname,concat(middle_name,' ',last_name),1)
UPDATE people_exit SET first_name = middle_name WHERE first_name = ''
UPDATE people_exit SET middle_name = '' WHERE first_name = middle_name
이러한 기능을 필요로 하는 유일한 경우는 Firstname과 Lastname을 다른 필드로 저장하도록 테이블을 변경하는 UPDATE 쿼리입니다.
데이터베이스 설계는 특정 규칙을 따라야 하며 데이터베이스 정규화는 가장 중요한 규칙 중 하나입니다.
저는 성과 이름이 모두 한 칸에 있는 칼럼을 가지고 있었습니다.이름과 성이 쉼표로 구분되었습니다.다음 코드는 동작했다.에러 확인/수정 없음.그냥 멍청한 분열이야.phpMyAdmin을 사용하여 SQL 문을 실행.
UPDATE tblAuthorList SET AuthorFirst = SUBSTRING_INDEX(AuthorLast,',',-1) , AuthorLast = SUBSTRING_INDEX(AuthorLast,',',1);
여기서 smhg와 MySQL에서 지정된 서브스트링의 마지막 인덱스에서 curt를 가져와 결합합니다.이것은 mysql의 경우입니다.first_name last_name으로 이름을 분할하면 됩니다.성(last_name)은 단일 단어, 그 단일 단어 앞에 있는 이름 모두입니다.여기서 이름은 null, 1워드, 2워드 또는 2워드 이상일 수 있습니다.즉, 무효, Mary, Mary Smith, Mary A.스미스; 메리 수 엘렌 스미스;
따라서 이름이 1단어 또는 null이면 last_name은 null이 됩니다.이름이 1word보다 큰 경우 last_name은 마지막 단어이고 first_name은 마지막 단어 앞에 있는 모든 단어입니다.
Joe Smith Jr.나 Joe Smith Esq 같은 것은 이미 수동으로 잘라냈다는 점에 유의해 주십시오.물론 힘들었지만 그렇게 할 수 있을 만큼 작았기 때문에 어떤 방법을 사용할지 결정하기 전에 이름 필드의 데이터를 잘 살펴보시기 바랍니다.
이렇게 하면 결과도 트리밍되므로 이름 앞이나 뒤에 공백이 생기지 않습니다.
제가 필요한 걸 찾아 구글로 찾아오는 사람들을 위해 이 글을 올렸습니다.물론 이 방법은 먼저 선택 항목을 사용하여 테스트합니다.
단 한 번의 일이기 때문에 효율은 신경 쓰지 않습니다.
SELECT TRIM(
IF(
LOCATE(' ', `name`) > 0,
LEFT(`name`, LENGTH(`name`) - LOCATE(' ', REVERSE(`name`))),
`name`
)
) AS first_name,
TRIM(
IF(
LOCATE(' ', `name`) > 0,
SUBSTRING_INDEX(`name`, ' ', -1) ,
NULL
)
) AS last_name
FROM `users`;
UPDATE `users` SET
`first_name` = TRIM(
IF(
LOCATE(' ', `name`) > 0,
LEFT(`name`, LENGTH(`name`) - LOCATE(' ', REVERSE(`name`))),
`name`
)
),
`last_name` = TRIM(
IF(
LOCATE(' ', `name`) > 0,
SUBSTRING_INDEX(`name`, ' ', -1) ,
NULL
)
);
데이터가 first_name 필드에 모두 도착했을 때 first_name과 last_name으로 분할하는 방법을 사용했습니다.이렇게 하면 성 필드에 마지막 단어만 표시되므로 "john phillips sousa"는 "john phillips"의 이름과 "sousa" 성이 됩니다.또한 이미 수정된 레코드를 덮어쓰지 않아도 됩니다.
set last_name=trim(SUBSTRING_INDEX(first_name, ' ', -1)), first_name=trim(SUBSTRING(first_name,1,length(first_name) - length(SUBSTRING_INDEX(first_name, ' ', -1)))) where list_id='$List_ID' and length(first_name)>0 and length(trim(last_name))=0
UPDATE `salary_generation_tbl` SET
`modified_by` = IF(
LOCATE('$', `other_salary_string`) > 0,
SUBSTRING(`other_salary_string`, 1, LOCATE('$', `other_salary_string`) - 1),
`other_salary_string`
),
`other_salary` = IF(
LOCATE('$', `other_salary_string`) > 0,
SUBSTRING(`other_salary_string`, LOCATE('$', `other_salary_string`) + 1),
NULL
);
누군가 테이블을 뛰어 넘어 필드를 분할해야 하는 경우:
- 먼저 위의 기능을 사용합니다.
CREATE DEFINER=`root`@`localhost` FUNCTION `fn_split_str`($str VARCHAR(800), $delimiter VARCHAR(12), $position INT) RETURNS varchar(800) CHARSET utf8
DETERMINISTIC
BEGIN
RETURN REPLACE(
SUBSTRING(
SUBSTRING_INDEX($str, $delimiter, $position),
LENGTH(
SUBSTRING_INDEX($str, $delimiter, $position -1)
) + 1
),
$delimiter, '');
END
- 둘째, 결과가 나오지 않을 때까지 문자열에서 while loop을 실행합니다(JOIN 절에 $id를 추가했습니다).
CREATE DEFINER=`root`@`localhost` FUNCTION `fn_split_str_to_rows`($id INT, $str VARCHAR(800), $delimiter VARCHAR(12), $empty_table BIT) RETURNS int(11)
BEGIN
DECLARE position INT;
DECLARE val VARCHAR(800);
SET position = 1;
IF $empty_table THEN
DROP TEMPORARY TABLE IF EXISTS tmp_rows;
END IF;
SET val = fn_split_str($str, ',', position);
CREATE TEMPORARY TABLE IF NOT EXISTS tmp_rows AS (SELECT $id as id, val as val where 1 = 2);
WHILE (val IS NOT NULL and val != '') DO
INSERT INTO tmp_rows
SELECT $id, val;
SET position = position + 1;
SET val = fn_split_str($str, ',', position);
END WHILE;
RETURN position - 1;
END
- 마지막으로 다음과 같이 사용할 수 있습니다.
DROP TEMPORARY TABLE IF EXISTS tmp_rows;
SELECT SUM(fn_split_str_to_rows(ID, FieldToSplit, ',', 0))
FROM MyTable;
SELECT * FROM tmp_rows;
ID를 사용하여 다른 테이블에 가입할 수 있습니다.
하나의 값만 분할하는 경우 그렇게 사용할 수 있습니다.
SELECT fn_split_str_to_rows(null, 'AAA,BBB,CCC,DDD,EEE,FFF,GGG', ',', 1);
SELECT * FROM tmp_rows;
임시 테이블은 비울 필요가 없습니다.기능에서 처리해 드릴 겁니다.
mysql 5.4는 네이티브 분할 함수를 제공합니다.
SPLIT_STR(<column>, '<delimiter>', <index>)
언급URL : https://stackoverflow.com/questions/2696884/split-value-from-one-field-to-two
'source' 카테고리의 다른 글
| Gradle - 기본 매니페스트 속성 없음 (1) | 2022.09.25 |
|---|---|
| MySQL에서 중첩된 트랜잭션이 허용됩니까? (0) | 2022.09.25 |
| AWS RDS 연결 중단됨 Haproxy (0) | 2022.09.25 |
| MariaDb SQL 주입 (1) | 2022.09.25 |
| 2개의 NULL 포인터를 감산하는 동작이 정의되어 있습니까? (1) | 2022.09.25 |