Answered by:
Combining cells and doing math

I have a table that has a column with text in it. I need to combine the text that has the same id, get the length of the text, and then perform some math.
Create/Populate [#ResultText]: if object_id('tempdb..[#ResultText]','U') is not null drop table [#ResultText] go create table [#ResultText] ( [resulttextid] int ,[resultid] int ,[resulttextorder] int ,[resulttexttext] varchar(max) ) go set nocount on insert [#ResultText] select 1, 50, 1, 'the quick brown fox jumped over the lazy dogs.' insert [#ResultText] select 2, 50, 2, 'Big dwarves heckle my top quiz of jinx.' insert [#ResultText] select 3, 51, 1, 'Vamp fox held quartz duck just by wing.' insert [#ResultText] select 4, 51, 2, 'and a few more' insert [#ResultText] select 5, 52, 1, 'Five jumping wizards hex bolty quick.' go  select * from [#ResultText]
So what I want to do is take the ResultTextText for the rows that have 50 for the resultid and combine them. I need to get the total length of the characters (45+38=83). Then I need to divide by 10, always rounding up. So I would get 8.3, with the file returned result as 9.
For 51, there are 53 characters, divided by 10 and round up and you get 6.
For 52, there are 37 characters, divided by 10 and round up and you get 4.
I am using C# to perform the query, so would it be better to perform all of this in SQL, or just I get the text result and use C# to do the math?
Question
Answers

Try using CEILING function , if the rounding needed to be next possible integer value.
select resultid,CEILING(sum(len(resulttexttext))/10.0) from #ResultText group by resultid
Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.
 Proposed as answer by Allen Li  MSFTModerator Sunday, July 21, 2013 7:33 AM
 Marked as answer by MatMDIG Monday, July 22, 2013 4:18 PM
All replies



Try using CEILING function , if the rounding needed to be next possible integer value.
select resultid,CEILING(sum(len(resulttexttext))/10.0) from #ResultText group by resultid
Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.
 Proposed as answer by Allen Li  MSFTModerator Sunday, July 21, 2013 7:33 AM
 Marked as answer by MatMDIG Monday, July 22, 2013 4:18 PM