Is it possible to make an addition with varchar field?

Asked

Viewed 45 times

0

I have the following update:

update ex      
set ex.seq_docum_ref = '00001',    
    ex.nr_chv_nfe_ref = ref.ChaveNfTerceiro,         
    ex.cd_docum_ref = case when ChaveNfTerceiro is not null then coalesce(ref.DocSispro,'') else case when ref.SerieNotaProd is not null then 'NFP'+cast(ref.SerieNotaProd as varchar) else '' end end,         
    ex.cd_docum_nr_ref = ref.NrNfTerceiro,         
    ex.cd_pessoa_ref = ex.cd_pessoa_emi,         
    ex.dt_nota_ref = replace(convert(varchar, ref.DtNfTerceiro,103),'/',''),         
    ex.in_ent_sai = 'S'

in the field

ex.seq_docum_ref

I want it to add one more to each update performed, however the field is varchar, how can I make this addition and keep the field type? I need it to deliver an assignment to work. Thanks in advance!

sql-server

  • You can do the operation with a Convert, but the ideal is to change this field to a numeric.

  • @gmsantos Could exemplify the operation with Convert?

  • @Renangoulart SELECT CONVERT(int, '42') + CONVERT(int, '42') returns 84 for example.

  • For your case, it would be better to take a look at the definition of trigger, is what you’ll need.

  • @Thrnk I was wondering how I would apply this in this update, I would have some example please?

1 answer

2


If I didn’t miss the syntax, try something like

UPDATE ex      
  set seq_docum_ref= right (('0000' + cast ((cast (seq_docum_ref as int) +1) as varchar(5))), 5), 
  ...

But the best thing is to use an auxiliary table to store the counter as a numerical value and, whenever it is necessary to obtain the next sequence, request it and then store it in the table by converting it to string with zeros on the left. In the article Generation of numerical sequences you find ways to generate a reliable sequence.

Browser other questions tagged sql-server

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