Archive for April, 2025

SQL: sed-like replacement of a string

Wednesday, April 30th, 2025

Imagine the situation: you have an SQL table with some string in some column. There is a plenty of such rows, and you want to replace this string in each row with some other string, something like in Bash – sed ‘s/green apple/blue apple/g’.

The original table looks like this.

Select:

SELECT "prefix", "description", "ruleid" 
FROM "dr_rules" 
WHERE "description" LIKE '%from ACME%' 
ORDER BY "ruleid" 
DESC LIMIT 5;

Result:

prefix	        description
73843444445	Novokuznetsk from ACME
73843208507	Novokuznetsk from ACME
73843400000	Maxim Novokuznetsk  from ACME
73843208505	Office Poehali Novokuznetsk  from ACME
73843208508	Office Maxim Novokuznetsk  from ACME

We want to change “from ACME” to “moved from ACME”.

First, let’s test our SQL statement with SELECT, no changes will be made:

SELECT REPLACE(description,'from ACME','moved from ACME') 
FROM dr_rules
WHERE description LIKE('%from ACME%')
AND description NOT LIKE('%moved from ACME');

Now do an SQL UPDATE in a sed-like style:

UPDATE dr_rules 
SET description=REPLACE(description,'from ACME','moved from ACME')
WHERE description LIKE('%from ACME%')
AND description NOT LIKE('%moved from ACME');

The result after an UPDATE:

prefix	        description
73843444445	Novokuznetsk moved from ACME
73843208507	Novokuznetsk moved from ACME
73843400000	Maxim Novokuznetsk  moved from ACME
73843208505	Office Poehali Novokuznetsk  moved from ACME
73843208508	Office Maxim Novokuznetsk  moved from ACME

So, to test the statement, do:

SELECT REPLACE(columnname,'old string','new string') 
FROM tablename
WHERE columnname LIKE('%old string%')
AND columnname NOT LIKE('%new string');

To replace, do:

UPDATE tablename 
SET columnname=REPLACE(columnname,'old string','new string')
WHERE columnname LIKE('%old string%')
AND columnname NOT LIKE('%new string');

If you need case-insensitive, use ILIKE instead of LIKE.

Tags:,
Posted in Uncategorized | Comments Closed

Asterisk: skip some action within timeout after a call

Tuesday, April 29th, 2025

Let’s skip playing an annoying greeting for the caller, if he already called us withing the last 10 minutes.

Create a macro:

macro greetBlock( ext )
{
    if(${DB_EXISTS(greetblock/${CALLERID(num)})}) {
        // get the last call's timestamp.
        Set(LASTCALL=${DB(greetblock/${CALLERID(num)})});
    } else {
        Set(LASTCALL=0);
    }

    Set(NOW=${EPOCH});
    Set(DIFF=${MATH(${NOW} - ${LASTCALL},int)});

    if ("${LASTCALL}" != "" & ${DIFF} < 600) {
        // a person called less than 10 minutes ago - skip greeting.
        NoOp(skip greeting to ${CALLERID(num)} for ${ext});
        Set(GREETING=0);
    } else {
        Set(DB(greetblock/${CALLERID(num)})=${NOW});
        Set(GREETING=1);
    }

    return;
};

And use this macro in your dialplan:

73333123456 =>
{
  NoOp(${STRFTIME(,,%F %R)} . ${CALLERID(num)} dials SALES office . callid start: ${SIP_HEADER(Call-ID)});

  &greetBlock(${EXTEN});
    
  if ("${GREETING}" = "1") {
    goto greeton;
  } else {
    goto greetoff;
  }

  greeton:
    Playback(${SOUNDS}/welcome);

  greetoff:
    Queue(...);
    ...
}

Tags:, , , ,
Posted in Uncategorized | Comments Closed