In varchar column add numbers and show strings

Question

In varchar column add numbers and show strings

msbisqldeveloper

Valued Member

Points: 63
More actions
April 8, 2020 at 11:19 pm

#3741622

Below is the sample data
create table temp (userid int , rid varchar(10), value varchar(100))
insert into temp(userid,rid,value)
values(1,'D01','3'),
(1,'D01','4'),
(2,'C01','hey'),
(2,'C01','1')
expected output:
1,'D01','7'
2,'C01','hey'
2,'C01','1'
I tried below code and it is throwing error
select distinct userid,rid,
case when ISNUMERIC(value) = 1
THEN
SUM(cast(value as int))
over (partition by userId,rid order by userid)
else value
end as [Value]
from temp

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Steve Collins SSCrazy Points: 2157 More actions · Answer 1

drop table if exists #temp;
go
create table #temp (
  userid int , rid varchar(10), value varchar(100));

insert #temp(userid,rid,value) values
(1,'D01','3'),
(1,'D01','4'),
(2,'C01','hey'),
(2,'C01','1');

with t_cte(userid,rid,value) as (
    select
      userid, rid, cast(sum(cast(value as int)) as varchar)
    from
      #temp
    where
      isnumeric(value)=1
    group by
      userid, rid
    union all
    select * from #temp where isnumeric(value)=0)
select * from t_cte order by 1, 2;

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Phil Parkin SSC Guru Points: 246960 More actions · Answer 2

This look very similar to this post. Why did you post again?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

msbisqldeveloper Valued Member Points: 63 More actions · Answer 3

msbisqldeveloper

Valued Member

Points: 63

April 9, 2020 at 12:53 pm

#3741740

Thanks