MUMPS $Functions in SQL Statements

Question

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..."

Answer 1

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.

Answer 2

MUMPS itself does not have any support of SQL, but in Caché it is possible to use some of system functions. But in SQL you should use full name for such functions, so $p is not available for use. All list of available functions you can find in the documentation here.