How to copy data from one column to another column of the same table

Asked

Viewed 42 times

-2

Hello, good afternoon.

I have a column table called City where inside it has 5 fields(Codecompleto(PK), Code, Name, State, Nameclear).

This Nameclear was created to receive the Name without accents.

I created a FUNCTION that removes this accent and when I give the select in dbo.city.name it brings in alphabetical order.

What I want to do is popular this column Nameclear with the result of the FUNCTION search... would be possible?

sql database sql-server

  • Have you tried UPDATE City SET NameClear = function(Name)?

1 answer

0


A way to make the column NameClear contains the same content as in the column Name, but without the accentuation, is to declare it as calculated column. Something like this:

NameClear as dbo.SemAcento (Name)

There are advantages and disadvantages to this approach.

Another option is to create trigger in the table City where the contents of the column Name is changed the same occurs in the column NameClear, by applying the function that removes accentuation.

CREATE TRIGGER dbo.retira_acento_Name
     on dbo.City
     after INSERT, UPDATE
as
begin
declare @NI integer, @ND integer;
set @NI= (SELECT count(*) from (SELECT top (2) * from INSERTED) as I);
set @ND= (SELECT count(*) from (SELECT top (2) * from DELETED) as D);

IF @NI > 0 and @ND = 0 
  -- trata inclusao
  begin 
  UPDATE C
    set NameClear= dbo.SemAcento (I.Name)
    from INSERTED as I
         inner join dbo.City as C on C.CodeCompleto = I.CodeCompleto;
  end

else
IF @NI > 0 and @ND > 0
  -- trata alteração
  begin
  IF UPDATE (Name)
    begin
    UPDATE C
      set NameClear= dbo.SemAcento (I.Name)
      from INSERTED as I
           inner join DELETED as D on D.CodeCompleto = I.CodeCompleto 
           inner join dbo.City as C on C.CodeCompleto = D.CodeCompleto
      where I.Name <> D.Name;
    end;
  end;
end;
go

I have not tested; I hope you have no mistake(s).

Regarding withdraw accentuation, I suggest reading the article "Remove accent and "invisible characters"that contains interesting solutions on the subject.

Browser other questions tagged sql database sql-server

You are not signed in. Login or sign up in order to post.