Search code examples
intersystems-cacheintersystemsmumpsobjectscript

MUMPS $Functions in SQL Statements

Can $Functions like $Extract, $P be used in SQL statements? If they can, please provide and example. Ex Select $P(Field1,"*") from MyTable. This returns an error saying "A term expected beginning with either..."

Solution

  • If your question is can I use some of the standard $ functions in CacheSQL the answer is yes. The following $ functions are available in CacheSQL

    SQL functions: $EXTRACT $FIND $LENGTH $LIST

    I suspect your issue is the usage of " characters.
    If you did 

    SELECT $PIECE(Name,',',1) AS LastName,
       $PIECE(Name,',',2) AS FirstName
FROM   Sample.Person

    you should get results.