question

RIMM1234 avatar image
RIMM1234 asked

SQL query

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.

query
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

anthony.green avatar image
anthony.green answered

CASE LEN(message) WHEN <=160 THEN 1 ELSE 2 END

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

bschaper avatar image
bschaper answered

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


10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.