Adding 2 varchar(2) columns

Transact-SQL
1

Hi

I am trying to add the 2 columns both varchar(2) and need to sum the 2 columns like 5+3=8 but its not adding them its concatenating them, I have tried so many conversion but not working because of conversion to INT,

CAST(COnvert(varchar,A.E1CODE) AS INT) + CAST(COnvert(varchar,AJOB_Code) AS INT)
from your table

image

connversion failed when converting the varchar value 'N' to data type int.

what is the best way to add (sum)these 2 columns in the SELECT Satement? Probabaly something simple I am missing?
Thanks.
Pasi

2

what is the numeric equivalent value of N. you cant convert N to int

3

Thats how data is, if it has a string then add the string to next column Like N00 and if there are numbers like 5 and 3 then is 8. any way to do this?

4

so if it is
N, 00 = N00
2,02=202

etc?

5

almost;
so if it is
N, 00 = N00
2,02=202 -->> BECOMES 4

6

:roll_eyes: what if it is another alphabet other than N. what if jobcode is N

7

Havent see another Chars, but whole point is to add the 2 columns together, if its N,00 then N00, if 3,05 then 5..

8

the point is we want to provide you the correct answer. hold one second

9 
create table #pasi_oracle_migration(E1code varchar(2), job_code varchar(2))

insert into #pasi_oracle_migration
select '5', '02' union
select 'N', '00'


select E1code, job_code,
       case
	      when ISNUMERIC(E1code) = 1 
then   cast(CAST(E1CODE AS INT) + CAST(job_code AS INT) as varchar(150))
		  else E1code + job_code
	   end as bootleg
  from #pasi_oracle_migration

drop table #pasi_oracle_migration
10

Thanks Works fine.! tried TRY_CAST but the 2nd part gets NULL

11

TRY_CAST

Return Types
Returns a value cast to the specified data type if the cast succeeds; otherwise, returns null.

12

Yeah, it looks like it, so I guess this has to be with a case , there is no other one line formula to do this?
Pasi.