💵 Enter the coupon code BLACKFRIDAY before 30th of November for a 25% discount on any new SMS Server license license 💵

How can we help?

Trigger, routing and blocking conditions


Conditions in the Auron SMS Server are SQL expressions. You’ll find conditions when creating:

  • Triggers. To automatically process messages;
  • Routing rules. To route messages to specific channels;
  • Blocking rules. To block outgoing messages.

The conditions always specify to which messages a rule should apply.

Message views use SQL expressions as well. In the case of views we call them a filter. But all of the below rules apply to these filters as well.

This document explains some of the rules that apply to writing a condition or a filter.

Basic syntax and operators

If you’re not familiar with SQL here’s just a very short overview of the basic syntax and operators available.

SQL requires strings to be expressed using single quotes. Boolean values are 1 for true and 0 for false.

All SQL operators can be used. Find a full list of operator here.

A short list of useful operators:

OperatorExampleDescription  
=ToAddress = '+316211223344'Equal to
<Retries < 3Less than
>Retries > 3Greater than
<>IsDeliveryReport <> 0Not equal to
+Retries + 1 < 3Addition for numbers or concatenation for strings
-Retries - 1 < 3Subtraction
%ID % 5 = 0Modulo
LIKEBody LIKE 'QUIZ%'Compare a string against a pattern. '%' means zero, one or multiple characters. '_' means 1 character
INToAddress IN ('+31611223344', '+31611223355', '+31611223366')Test if a field compares to one value out of the specified list
ANDToAddress = 'email@yourdomain.com' AND FromAddress = 'email@yourdomain.com'Combine expressions such that the expression is true if all parts of the expression are true
ORToAddress = 'sales@yourdomain.com' OR ToAddress = 'contact@yourdomain.comCombine expressions such that the expressions is true if one part of the expression is true
NOTBody NOT LIKE 'QUIZ%'Reverse the expression
EXISTSEXISTS (SELECT ID FROM Channels WHERE ID='SMPP1' AND Enabled <> 0)True if a sub-query contains any rows

Functions

You can use SQL functions in your SQL expression as well. Find a full list of all functions here.

A short list of useful functions:

FunctionExampleDescription  
SYSUTCDATETIME()LastUpdate > DATEADD(d, -7, SYSUTCDATETIME())Returns the current date/time in UTC. The SMS Server database records all date/time values in UTC.
DATEADD(datepart, number, date)LastUpdate > DATEADD(d, -7, SYSUTCDATETIME())Add time to a given date and return the new value.
The datapart can be:
n - Minutes
hh - Hours
d - Days
m -Months
yy - Years
The number can be negative to subtract. The date can be a data field or an expression that returns a date.
RAND()RAND() < 0.5Generate a random number between 0 and 1
CONVERT(datatype, expression)CONVERT(INT, RAND() * 100) % 10 = 1Converts the expression into the specified datatype.
Use it to convert date to string or floats to integers so you can use the modulo operator.
fnRouteProportional(randnr, lanes)fnRouteProportional(RAND(ID), 4)Returns a random number between 0 and lanes - 1. This function is intended to do proportional routing inside routing rules. When called as per example the returned number will be the same for the same ID.
fnNumQueued(channelid)fnNumQueued(ChannelID)Returns the number of queued messages on a given channel.