This is documentation for Caché & Ensemble. See the InterSystems IRIS version of this content.

For information on migrating to InterSystems IRIS, see Why Migrate to InterSystems IRIS?


class %TSQL.Impl extends %Library.RegisteredObject

Implementation of TSQL scalar and other functions not supported by Cache SQL

Method Inventory


classmethod CHARINDEX(ToFind As %String, FindIn As %String, StartAt As %Integer = 1) as %String [ SQLProc = CHARINDEX ]

Implement CHARINDEX(tofind,findin[,startat])

classmethod DATALENGTH(Item) as %Integer [ SQLProc = DATALENGTH ]

Implement DATALENGTH(item)

classmethod FLOOR(Number As %String) as %Numeric

Implement FLOOR for COS context

classmethod Find(LookIn As %String, LookFor As %String, StartLook As %Integer = 1)

Look for LookIn in LookFor starting at StartLook (defaults to 1)

If the string isn't found then 0 is returned

If the string is found then the 1-based offset to the start of LookFor in LookIn is returned

classmethod IFNULL(Expr1 As %String, Expr2 As %String, Expr3 As %String = "") as %String [ SQLProc = IFNULL ]

Implement IFNULL(expr1,expr2[,expr3])

classmethod IsStream(Value As %String, ByRef StreamOref) as %Boolean

Is the given value a stream id?

- if so: return 1 and set StreamOref

- otherwise: return 0 and set StreamOref to $$$NULLOREF

classmethod LEN(Str As %String) as %Integer

Implement LEN for COS context

classmethod REPLACE(Expr As %String, Old As %String, New As %String) as %String [ SQLProc = REPLACE ]

Implement REPLACE(expr,old,new)

classmethod ROUND(Number As %Numeric, Length As %Integer, Function As %Integer) as %String [ SQLProc = ROUND ]

Implement ROUND(number,length,function)

classmethod STUFF(SourceString As %String, StartPos As %Integer, ReplaceLength As %Integer, ReplaceString As %String) as %String [ SQLProc = STUFF ]

Implement STUFF(sourcestring,startpos,replacelength,replacestring)

classmethod SUBSTRING(Item, Start, Length) as %String [ SQLProc = SUBSTRING ]

Implement SUBSTRING(item, start, length)

classmethod ZBITWISEAND(A, B) as %Integer [ SQLProc = ZBITWISEAND ]

Implement bitwiseAND

classmethod ZBITWISEOR(A, B) as %Integer [ SQLProc = ZBITWISEOR ]

Implement bitwiseOR

classmethod ZBITWISEXOR(A, B) as %Integer [ SQLProc = ZBITWISEXOR ]

Implement bitwiseXOR


Translate a TSQL error code to a SQLCODE

classmethod ZFORMATMESSAGE(Message, Args...) [ SQLProc = ZFORMATMESSAGE ]

Format a RAISERROR message with the given arguments

classmethod ZLIKETOCOSPATTERN(LikePattern As %String, LikeEscape As %String = "") as %String

Given a LIKE pattern and optional LIKE ESCAPE, return the corresponding COS pattern (non-SQL strings)

classmethod ZLIKETOCOSPATTERNS(LikePattern As %String, LikeEscape As %String = "") as %String [ SQLProc = ZLIKETOCOSPATTERNS ]

Given a LIKE pattern and optional LIKE ESCAPE, return the corresponding COS pattern (SQL strings)

classmethod ZPATINDEXFB(CosPattern As %String, Expr As %String) as %Integer [ SQLProc = ZPATINDEXFB ]

PATINDEX for anything that isn't covered above (FB=fallback) - returns 0/1

- will not give the correct result for a pattern beginning with "%"

classmethod ZPATINDEXL(LiteralPart As %String, Expr As %String) as %Integer [ SQLProc = ZPATINDEXL ]

PATINDEX for: literal - returns 0/1

classmethod ZPATINDEXLR(LiteralPart As %String, CosPattern As %String, Expr As %String) as %Integer [ SQLProc = ZPATINDEXLR ]

PATINDEX for: literal rest - returns 0/1

classmethod ZPATINDEXPL(LiteralPart As %String, Expr As %String) as %Integer [ SQLProc = ZPATINDEXPL ]

PATINDEX for: percent literal - returns 0/position of literal

classmethod ZPATINDEXPLR(LiteralPart As %String, CosPattern As %String, Expr As %String) as %Integer [ SQLProc = ZPATINDEXPLR ]

PATINDEX for: percent literal rest - 0/returns position of literal

classmethod ZPATINDEXPN(CosPattern As %String, Expr As %String) as %Integer [ SQLProc = ZPATINDEXPN ]

PATINDEX for: percent non-literal - returns 0/position of non-literal

classmethod ZPATINDEXU(UnderlineCount As %Integer, Expr As %String) as %Integer [ SQLProc = ZPATINDEXU ]

PATINDEX for: underline(s) - returns 0/1

classmethod ZPATINDEXUL(UnderlineCount As %Integer, LiteralPart As %String, Expr As %String) as %Integer [ SQLProc = ZPATINDEXUL ]

PATINDEX for: underline(s) literal - returns 0/1

classmethod ZPATINDEXULR(UnderlineCount As %Integer, LiteralPart As %String, CosPattern As %String, Expr As %String) as %Integer [ SQLProc = ZPATINDEXULR ]

PATINDEX for: underline(s) literal rest - returns 0/1

classmethod ZPATTERNMATCH(Expr As %String, CosPattern As %String) as %Integer [ SQLProc = ZPATTERNMATCH ]
classmethod ZRAND(ScaleFactor As %Integer) as %Float [ SQLProc = ZRAND ]

Implement ZRAND(scalefactor)

classmethod ZREMAMPM(Item) as %String [ SQLProc = ZREMAMPM ]

Implement ZREMAMPM(item) to remove the "AM" or "PM" if necessary

classmethod ZRPATINDEX(TsqlPattern As %String, Expr As %String) as %Integer [ SQLProc = ZRPATINDEX ]

PATINDEX for non-literal patterns

classmethod ZRPATINDEXPLP(TsqlInnerPattern As %String, Expr As %String) as %Integer [ SQLProc = ZRPATINDEXPLP ]

PATINDEX for "%"+XXX+"%" patterns

classmethod ZWITHDATETIME(Item As %String) as %String [ SQLProc = ZWITHDATETIME ]

Implement ZWITHDATETIME(item) to append a date of 1/1/1900 and/or a time of midnight to the item if necessary

Inherited Members

Inherited Methods