Line break in string in Stream Analytics job

Question

Hello.

I have a stream analytics job that generates alarm messages on incoming data and sends them to the Message table where they get picked up by Twilio Text Message service.
Here's my Stream Analytics query:

case when max(lab_temp_alarm) = 1 or max(humidity_alarm) = 1
then concat('ALERT: Temperature or Humidity value over threshold',
'; Time: ', max(timestamp_cst), ' CST', <line break>,
'; Asset Name: ', asset_name, <line break>,
'; Node Name: ', node_name, <line break>,
'; Temperature: ', round(avg([temperature (F)]), 2), 'F', <line break>,
'; Humidity: ', round(avg(humidity), 2), '%')
else 'No alarms'
end as TextBody

I need to put line breaks between "Time", "Asset Name" and all other readings. In SQL I would use CHAR(13) but it's not supported in Stream Analytics.
I tried to put "\n", "\r\n", "x0a" and all other stuff but they're not converted into new line in the text messages that I receive during testing, they just remain what they are.
If there are line breaks in a message body in SQL so is there any alternative to CHAR(13) in Stream Analytics?

Thanks!

Answer 1

Hello @Jane Dickson ,

Thanks for the ask and using Microsoft Q&A platform .
When i see your query , I see that you are concatenating the fields to make a string and that was the Twillio API are going to consume . Since its passed as a string its will take abd char(13) and not as a new line character . The solution which I am sharing is a overkill ( as per me ) but hopefully its gives you an idea and you can improve on the same .

I my case the I have the records in EH and I created a query something like this .

select concat('''device 1:',deviceId ,''' + char(13) +','''device 2:',deviceId,'''') as message
into SQLOP from EHInput

OP : Something like ( this is what is going to go in SQL table .

'device 1:2' + char(13) +'device 2:2'

IN SQL run the below query and you should have the required output .

DECLARE @message VARCHAR(50)
DECLARE @formattedmessage VARCHAR(256)

DECLARE db_cursor CURSOR FOR
SELECT message from ASATest

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @message

WHILE @@Fetch _STATUS = 0
BEGIN
SET @formattedmessage = (@message)
SELECT (@formattedmessage)
FETCH NEXT FROM db_cursor INTO @message
END

CLOSE db_cursor
DEALLOCATE db_cursor

----------

Please do let me know how it goes .
Thanks
Himanshu

-------------------------------------------------------------------------------------------------------------------------

• Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators