Separate a column of the database in two and take the values of users already created

Question

I have a table of users in the database with a column NAME, but it will be necessary to separate between NAME and SURNAME. My question is: will users already created in the bank have to be edited one by one? Or have some command that I can break the user name in two and play the second part of the name for the new SURNAME column??

Currently: Name: Rodrigo Barreto Objective: Name: Rodrigo , Surname: Barreto

Thank you in advance

Answer 1

Sim, you have some functions for string who do this:

Example:

select
    substring(trim(nome)  from 0 for position(' ' in trim(nome))) as firstname,
    substring(trim(nome) from position(' ' in trim(nome)) for char_length(trim(nome))) as lastname
from usuarios
where trim(nome) like '% %';

Sample data:

Upshot:

I put in the Sqlfiddle

Update Command would look like this:

update usuarios set 
    nome = substring(trim(nome)  from 0 for position(' ' in trim(nome))),
    sobrenome  = substring(trim(nome) from position(' ' in trim(nome)) for char_length(trim(nome)))
where sobrenome is null and trim(nome) like '% %';

Ps. The code may make you easier, but it will give you problems when it is a compound name or you do not have a surname. Example: "John Paul", or "John Paul"

Answer 2

Assuming your data structure is something like:

CREATE TABLE tb_usuario
(
    id INTEGER PRIMARY KEY,
    nome_completo TEXT,
    nome TEXT,
    sobrenome TEXT
);

Test Data:

-- DADOS PARA TESTE
INSERT INTO tb_usuario ( id, nome_completo ) VALUES ( 1, 'Rodrigo Barreto Silva' );
INSERT INTO tb_usuario ( id, nome_completo ) VALUES ( 2, 'Dino da Silva Sauro' );
INSERT INTO tb_usuario ( id, nome_completo ) VALUES ( 3, 'Bond James Bond' );
INSERT INTO tb_usuario ( id, nome_completo ) VALUES ( 4, 'Raimundo Nonato Santos' );

You can use a UPDATE clause-less WHERE using the functions substr() and split_part() combined to solve your problem:

-- ATUALIZANDO TABELA
UPDATE
    tb_usuario
SET
    nome = split_part( nome_completo, ' ', 1 ),
    sobrenome = substr( nome_completo, length(split_part( nome_completo, ' ', 1 ) ) + 2 );

Consulted updated data:

-- TABELA ATUALIZADA
SELECT 
    nome_completo,
    nome,
    sobrenome
FROM
    tb_usuario;

Exit:

|          nome_completo |     nome |      sobrenome |
|------------------------|----------|----------------|
|  Rodrigo Barreto Silva |  Rodrigo |  Barreto Silva |
|    Dino da Silva Sauro |     Dino | da Silva Sauro |
|        Bond James Bond |     Bond |     James Bond |
| Raimundo Nonato Santos | Raimundo |  Nonato Santos |

Sqlfiddle: http://sqlfiddle.com/#! 17/5aafb/1