I have an SMS MESSAGES TBALE. IT CONTAINS A COLUMN CALLED BODY, WHICH CONTAINS MESSAGE. I WANT TO WRITE A QUERRY TO COUNT THE CHARACTER LENGTH OF EACH MESSAGE. IF THE CHARACTER LENGTH IS BETWEEN 1-160 IT SHOULD BE COUNTED AS 1 MESSAGE. IF THE CHARACTER LENGTH OF THE MESSAGE IS MORE THAN 160, IT SHOULD BE COUNTED AS TWO MESSAGES.
CASE LEN(message) WHEN <=160 THEN 1 ELSE 2 END
The LEN() or LENGTH() function will return the number of characters in a string as an integer. You can then utilize a CASE statement to disposition as one or two messages.
Example:
SELECT
CASE WHEN LEN(BODY) <= 160 THEN 1
ELSE 2
END AS ‘MSG_CNT’
FROM SMS_TABLE
17 People are following this question.
