Copy a word from a column to the end

Question

I have several records in sql for example

almedia,jose costa santos

and I want to change the value to:

jose costa santos almeida

In other words, I want to take the last name at the beginning (before the comma) and put it at the end, but I don’t know how to do it.

Answer 1

You can use the Charindex() and the Substring() to do what you need.

First we must find the delimiting character, in your case the comma.

After that we will get what comes before and after the comma, this way:

select
    Substring(nome, Charindex(',', nome)+1, LEN(nome)) as Nome,
    Substring(nome, 1,Charindex(',', nome)-1) as LastName,
from Pessoas

Now, for what you need, just concatenate the two, getting like this:

select
    Substring(nome, Charindex(',', nome)+1, LEN(nome)) + ' ' + Substring(nome, 1,Charindex(',', nome)-1) as Nome
 from pessoas

At this Link has a small tutorial of the functions.

Answer 2

Randrade explained well and gave the solution for a SELECT , but if you really want to change and not simply select, I believe that a correct code is as follows:

update tabela set nome = 
substring(nome,charindex(',',nome)+1,len(nome)) + ' ' +
substring(nome,1 ,charindex(',',nome)-1)

It follows the same concept he explained to the SELECT

And an important tip from those who have been through a lot of pressure is always before an update that affects many records, test the query of a SELECT corresponding as an incorrect or accidental update may complicate your life and lead to data loss .

Answer 3

In your case you will have to create a function for this purpose. The function will receive the name, and search to the comma (which should be in the field), copy to a variable the part up to the comma, and the rest to another variable and concatenate the correct sequence. Simulating

variavel1= copia até a virgula;
variavel2= copia do restante após a virgula;
resultado = concatenar variavel2 e variavel1;

The idea is this.

Answer 4

You can do that with the two-function junction:

substring_index(string, delimitador, número da ocorrência do delimitador) that searches for the string you want and makes the split.

and

concat(string1, string2, string3, ...) to join the words.

SELECT

SELECT 
     CONCAT(SUBSTRING_INDEX(NOME, ',', -1), ' ', SUBSTRING_INDEX(NOME, ',', 1)) 
FROM 
     TABELA

ALTER

UPDATE
    TABELA
SET 
    NOME = CONCAT(SUBSTRING_INDEX(NOME, ',', -1), ' ', SUBSTRING_INDEX(NOME, ',', 1)) 

Exit

José Costa Santos Almeida

Answer 5

SOLUTION

declare @nam varchar(300) 
set @nam='Almeida,José Costa Santos ' 
select right(@nam, len(@nam)-charindex(',',@nam)) + ' ' +left(@nam, charindex(',',@nam)-1) as frist