Copy a word from a column to the end

You can use CharIndex () and < SubString () to do what you need.

First we must find the delimiter character, in its case the comma.

After this we will look for what comes before and after the comma, like this:

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, thus:

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

In this Link has a small tutorial of functions.

@Randrade explained well and gave solution to SELECT , but if you really want to change and not simply select, I believe 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 SELECT

And an important tip of those who have already been very tight is always before an update that affects many records, test the query of a corresponding SELECT since an incorrect or accidental update can complicate your life and lead to data loss .

In your case you will have to create a function for this purpose. The function will receive the name, and search the comma (which should be in the field), copy a variable to the point, and the remainder 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.

You can do this by joining two functions:

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

and

concat(string1, string2, string3, ...) to merge words.

SELECT

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

CHANGE

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

Output

  

José Costa Santos Almeida

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