한 필드에서 두 필드로 값 나누기

한 필드에서 두 필드로 값 나누기

---

membername성 및 이름이 모두 포함 된 테이블 필드 가 있습니다. 그것은 2 개에 그 분할 할 수 있는가 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로 설정합니다.
• 공백이 여러 개인 구성원 이름 값 : 첫 번째 공백 앞의 모든 것을 memberfirst에 추가하고 나머지 (추가 공백 포함)를 memberlast에 추가합니다.

업데이트 버전은 다음과 같습니다.

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
    );

---

If your plan is to do this as part of a query, please don't do that ^(a). Seriously, it's a performance killer. There may be situations where you don't care about performance (such as one-off migration jobs to split the fields allowing better performance in future) but, if you're doing this regularly for anything other than a mickey-mouse database, you're wasting resources.

If you ever find yourself having to process only part of a column in some way, your DB design is flawed. It may well work okay on a home address book or recipe application or any of myriad other small databases but it will not be scalable to "real" systems.

Store the components of the name in separate columns. It's almost invariably a lot faster to join columns together with a simple concatenation (when you need the full name) than it is to split them apart with a character search.

If, for some reason you cannot split the field, at least put in the extra columns and use an insert/update trigger to populate them. While not 3NF, this will guarantee that the data is still consistent and will massively speed up your queries. You could also ensure that the extra columns are lower-cased (and indexed if you're searching on them) at the same time so as to not have to fiddle around with case issues.

And, if you cannot even add the columns and triggers, be aware (and make your client aware, if it's for a client) that it is not scalable.

---

^(a) Of course, if your intent is to use this query to fix the schema so that the names are placed into separate columns in the table rather than the query, I'd consider that to be a valid use. But I reiterate, doing it in the query is not really a good idea.

---

It seems that existing responses are over complicated or not a strict answer to the particular question.

I think, the simple answer is the following query:

SELECT
    SUBSTRING_INDEX(`membername`, ' ', 1) AS `memberfirst`,
    SUBSTRING_INDEX(`membername`, ' ', -1) AS `memberlast`
;

---

I think it is not necessary to deal with more-than-two-word names in this particular situation. If you want to do it properly, splitting can be very hard or even impossible in some cases:

• Johann Sebastian Bach
• Johann Wolfgang von Goethe
• Edgar Allan Poe
• Jakob Ludwig Felix Mendelssohn-Bartholdy
• Petőfi Sándor
• 黒澤 明

In a properly designed database, human names should be stored both in parts and in whole. This is not always possible, of course.

---

use this

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX( `membername` , ' ', 2 ),' ',1) AS b, 
SUBSTRING_INDEX(SUBSTRING_INDEX( `membername` , ' ', -1 ),' ',2) AS c FROM `users` WHERE `userid`='1'

---

Not exactly answering the question, but faced with the same problem I ended up doing this:

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

---

In MySQL this is working this option:

SELECT Substring(nameandsurname, 1, Locate(' ', nameandsurname) - 1) AS 
       firstname, 
       Substring(nameandsurname, Locate(' ', nameandsurname) + 1)    AS lastname 
FROM   emp  

---

The only case where you may want such a function is an UPDATE query which will alter your table to store Firstname and Lastname into separate fields.

Database design must follow certain rules, and Database Normalization is among most important ones

---

I had a column where the first and last name were both were in one column. The first and last name were separated by a comma. The code below worked. There is NO error checking/correction. Just a dumb split. Used phpMyAdmin to execute the SQL statement.

UPDATE tblAuthorList SET AuthorFirst = SUBSTRING_INDEX(AuthorLast,',',-1) , AuthorLast = SUBSTRING_INDEX(AuthorLast,',',1);

13.2.10 UPDATE Syntax

---

This takes smhg from here and curt's from Last index of a given substring in MySQL and combines them. This is for mysql, all I needed was to get a decent split of name to first_name last_name with the last name a single word, the first name everything before that single word, where the name could be null, 1 word, 2 words, or more than 2 words. Ie: Null; Mary; Mary Smith; Mary A. Smith; Mary Sue Ellen Smith;

So if name is one word or null, last_name is null. If name is > 1 word, last_name is last word, and first_name all words before last word.

Note that I've already trimmed off stuff like Joe Smith Jr. ; Joe Smith Esq. and so on, manually, which was painful, of course, but it was small enough to do that, so you want to make sure to really look at the data in the name field before deciding which method to use.

Note that this also trims the outcome, so you don't end up with spaces in front of or after the names.

I'm just posting this for others who might google their way here looking for what I needed. This works, of course, test it with the select first.

It's a one time thing, so I don't care about efficiency.

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
    ) 
);

---

Method I used to split first_name into first_name and last_name when the data arrived all in the first_name field. This will put only the last word in the last name field, so "john phillips sousa" will be "john phillips" first name and "sousa" last name. It also avoids overwriting any records that have been fixed already.

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
    );

---

mysql 5.4 provides a native split function:

SPLIT_STR(<column>, '<delimiter>', <index>)

참고URL : https://stackoverflow.com/questions/2696884/split-value-from-one-field-to-two