SQL case statement to a better C# method

Question

I have code in SQL that will take the length of a SMS message and tell me how many parts the message will be.

Messages <= 160 characters are one part.

Messages > 160 characters become multipart messages, each part will then contain 152 characters. Can somebody please help me figure out a decent algorithm to accomplish something similar to the following in C# (lambda and linq expressions are welcome):

select  
    (case 
        when (LEN(Message)<=160) then 1
        when (LEN(Message)>160 and LEN(Message)<305) then 2
        when (LEN(Message)>304 and LEN(Message)<457) then 3
        when (LEN(Message)>456 and LEN(Message)<609) then 4
        when (LEN(Message)>608 and LEN(Message)<761) then 5
        when (LEN(Message)>760 and LEN(Message)<913) then 6
        when (LEN(Message)>912 and LEN(Message)<1065) then 7
        when (LEN(Message)>1064 and LEN(Message)<1217) then 8
        when (LEN(Message)>1216 and LEN(Message)<1369) then 9
        when (LEN(Message)>1368 and LEN(Message)<1521) then 10
        else 'n'
    end) as [Msg Parts]
from tblAuditLog

@Pierre-LucPineault - If it is multipart then ALL message parts have a 152 limit, even the first. — Ste, Aug 21 '13 at 14:23

score 2 · Accepted Answer · answered Aug 21 '13 at 14:20

2

(message.Length <= 160) ? 1 : (message.Length + 151) / 152)

assumes the message is a string named message. It takes care of rounding up (via the + 151) and uses integer division.

answered Aug 21 '13 at 14:20

FrankPl

13,205
2
14
40

eatonphil · Answer 2 · 2013-08-21T14:23:44.380

0

Why not just do it in your SQL? It really doesn't matter but you have no need to list out all the cases when it's a matter of simple division:

1+ (LEN(Message)-160)/152 AS [Msg Parts]

I'm not familiar with how division is handled in your SQL so you should also round up (LEN(Message)-160)/152 just to be safe.

edited Aug 21 '13 at 14:23

answered Aug 21 '13 at 14:18

eatonphil

13,115
27
76
133

This is a C# question, not a SQL question. The SQL code is just the "specification" of the requirement. – FrankPl Aug 21 '13 at 14:24
I don't have an issue with your answer, it's definitely correct. I just wanted to provide this option in case he overlooked it. I recognize that yours is the correct answer to the question. – eatonphil Aug 21 '13 at 14:25

score 0 · Answer 3 · answered Aug 21 '13 at 14:23

0

If you're looking for a raw translation, try:

from n in tblAuditLog
select new
{
    Parts = 
    (
        n.Length <= 160 ? 1 :
        n.Length > 160 ? 2 :
        n.Lenth > 304 ? 3 :
        //and so on
    )
}

answered Aug 21 '13 at 14:23

scourge192

1,909
16
22

SQL case statement to a better C# method

3 Answers3