none
Combining cells and doing math

    Question

  • 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?

    Friday, July 19, 2013 11:07 PM

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.

    Saturday, July 20, 2013 6:49 AM

All replies

  • The below query will result as you want.

    select resultid, sum(len(resulttexttext)) from  [#ResultText]

    group by resultid


    Thanks- Prajesh Please mark the post as answered if it answers your question

    Friday, July 19, 2013 11:47 PM
  • try this,

    select resultid, round(cast(resulttexttext as decimal)/10.0,0) resulttexttext from (
    select resultid, sum(len(resulttexttext)) resulttexttext from  [#ResultText]
    group by resultid
    ) x


    Regards, RSingh

    Saturday, July 20, 2013 2:14 AM
  • 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.

    Saturday, July 20, 2013 6:49 AM