Separate one column from the bank in two and pick up the values of the already created users

Question

Separate one column from the bank in two and pick up the values of the already created users

Navigation

#1 by (2 votes)
#2 by (2 votes)

3

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

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

Thank you in advance

ruby-on-rails postgresql

asked by anonymous 08.03.2018 / 15:40

2 answers

Conversion from Hex to Decimal using Complement of Two Is there a difference between "variableX: function () {...}" and "variableX = function () {...}"?

score 2 · Answer 1

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 with no WHERE clause using the combined substr() and split_part() functions 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 );

Queried updated data:

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

Output:

|          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: link

score 2 · Answer 2

S im, you have some functions for string that 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:

Result:

Iputitin SQLFiddle

The 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 can make it easier for you, but it will give you problems when it is a composite name or does not have a last name. Example: "John Paul", or "So-and-so"