Dividir valor de um campo para dois

Eu tenho um nome de membername campo de tabela que contém o sobrenome e o primeiro nome dos usuários. É possível dividi-los em dois campos? memberfirst , memberlast ?

Todos os registros têm este formato “Nome Sobrenome” (sem aspas e um espaço no meio).

Infelizmente, o MySQL não possui uma function de string dividida. No entanto, você pode criar uma function definida pelo usuário para isso, como a descrita no seguinte artigo:

  • MySQL Split String Function por Federico Cargnelutti

Com essa function:

 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 ; 

você seria capaz de construir sua consulta da seguinte maneira:

 SELECT SPLIT_STR(membername, ' ', 1) as memberfirst, SPLIT_STR(membername, ' ', 2) as memberlast FROM users; 

Se preferir não usar uma function definida pelo usuário e não se importar que a consulta seja um pouco mais detalhada, você também pode fazer o seguinte:

 SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(membername, ' ', 1), ' ', -1) as memberfirst, SUBSTRING_INDEX(SUBSTRING_INDEX(membername, ' ', 2), ' ', -1) as memberlast FROM users; 

Caso você não queira usar uma function, esta consulta lida com coisas mais limpas que as outras respostas:

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

Em comparação com outras respostas, essa abordagem cuida de:

  • valores de nome de membro sem um espaço : ele adicionará a cadeia inteira a memberfirst e definirá memberlast como NULL.
  • valores de nome do membro que têm vários espaços : ele adicionará tudo antes do primeiro espaço para memberfirst e o restante (incluindo espaços adicionais) para memberlast.

A versão UPDATE seria:

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

Se o seu plano é fazer isso como parte de uma consulta, não faça isso (a) . Sério, é um assassino de desempenho. Pode haver situações em que você não se importa com o desempenho (como trabalhos de migration únicos para dividir os campos, permitindo um melhor desempenho no futuro), mas, se você estiver fazendo isso regularmente para qualquer coisa que não seja um database de mickey-mouse, está desperdiçando resources.

Se você tiver que processar apenas parte de uma coluna de alguma forma, seu design de database é falho. Ele pode funcionar bem em um catálogo de endereços residencial ou em um aplicativo de receita ou em qualquer um dos muitos outros bancos de dados pequenos, mas não será escalável para sistemas “reais”.

Armazene os componentes do nome em colunas separadas. É quase invariavelmente muito mais rápido juntar colunas junto com uma simples concatenação (quando você precisa do nome completo) do que separá-las com uma pesquisa de caracteres.

Se, por algum motivo, você não puder dividir o campo, insira as colunas extras e use um acionador de inserção / atualização para preenchê-las. Embora não seja 3NF, isso garantirá que os dados ainda sejam consistentes e acelerem enormemente suas consultas. Você também pode garantir que as colunas extras tenham menos letras maiúsculas (e sejam indexadas se você estiver pesquisando nelas) ao mesmo tempo, para não ter que lidar com problemas de caso.

E, se você não puder adicionar as colunas e os gatilhos, esteja ciente (e conscientize seu cliente, se for para um cliente) de que não é escalonável.


(a) Naturalmente, se sua intenção for usar essa consulta para corrigir o esquema, de forma que os nomes sejam colocados em colunas separadas na tabela, em vez da consulta, considero isso como um uso válido. Mas reitero, fazê-lo na consulta não é realmente uma boa ideia.

Parece que as respostas existentes são complicadas demais ou não são uma resposta estrita à questão específica.

Eu acho que a resposta simples é a seguinte consulta:

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

Eu acho que não é necessário lidar com nomes de mais de duas palavras nesta situação particular. Se você quiser fazer isso corretamente, a divisão pode ser muito difícil ou até impossível em alguns casos:

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

Em um database adequadamente projetado, os nomes humanos devem ser armazenados em partes e no todo. Isso nem sempre é possível, claro.

usa isto

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

Não exatamente respondendo a pergunta, mas diante do mesmo problema acabei fazendo isso:

 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 

O único caso em que você pode querer tal function é uma consulta UPDATE que alterará sua tabela para armazenar o Nome e Sobrenome em campos separados.

O design do database deve seguir certas regras, e a Database Normalization está entre as mais importantes

No MySQL isso está funcionando esta opção:

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

Eu tinha uma coluna onde o primeiro e último nome estavam ambos em uma coluna. O primeiro e último nome foram separados por uma vírgula. O código abaixo funcionou. NÃO há verificação / correção de erros. Apenas uma separação idiota. Usado phpMyAdmin para executar a instrução SQL.

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

13.2.10 Sintaxe do UPDATE

Isso leva o smhg daqui e o curt de Last index de uma dada substring no MySQL e os combina. Isso é para o mysql, tudo que eu precisava era obter uma divisão decente do nome para first_name last_name com o sobrenome uma única palavra, o primeiro nome tudo antes dessa única palavra, onde o nome poderia ser null, 1 word, 2 words ou mais de 2 palavras. Ou seja: nulo; Maria; Mary Smith; Mary A. Smith; Mary Sue Ellen Smith;

Então, se name é uma palavra ou null, last_name é null. Se name for> 1 word, last_name é a última palavra e first_name todas as palavras antes da última palavra.

Note que eu já aparentei coisas como Joe Smith Jr.; Joe Smith Esq. e assim por diante, manualmente, o que era doloroso, é claro, mas era pequeno o suficiente para fazer isso, então você quer ter certeza de realmente olhar os dados no campo de nome antes de decidir qual método usar.

Observe que isso também reduz o resultado, para que você não fique com espaços à frente ou depois dos nomes.

Eu só estou postando isso para os outros que podem procurar o caminho do Google aqui procurando o que eu precisava. Isso funciona, claro, teste com o primeiro select.

É uma coisa de uma vez, então não me importo com eficiência.

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

Método Eu usei para dividir first_name em first_name e last_name quando os dados chegaram todos no campo first_name. Isto colocará apenas a última palavra no campo do sobrenome, então “john phillips sousa” será o primeiro nome de “john phillips” e o sobrenome de “sousa”. Evita também sobrescrever qualquer registro que já tenha sido corrigido.

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

O mysql 5.4 fornece uma function split nativa:

 SPLIT_STR(, '', )