/// <summary>
/// <para>CHARINDEX built-in function.</para>
/// <para>Searches an expression for another expression and returns its starting position if found.</para>
/// </summary>
/// <param name="toFind">Is a character expression that contains the sequence to be found. toFind is limited to 8000 characters.</param>
/// <param name="toSearch">Is a character expression to be searched.</param>
/// <param name="startLocation">Is an integer or bigint expression at which the search starts. If startLocation is not specified, is a negative number, or is 0, the search starts at the beginning of toSearch.</param>
public static SysFn Charindex(ScalarArgument toFind, ScalarArgument toSearch, ScalarArgument startLocation = null)
{
toFind = toFind ?? Designer.Null;
toSearch = toSearch ?? Designer.Null;
if (startLocation == null)
{
return(new SysFn((buildContext, buildArgs) =>
{
var sql = String.Format(
"CHARINDEX({0},{1})",
toFind.Build(buildContext, buildArgs),
toSearch.Build(buildContext, buildArgs));
buildContext.TryTakeException(toFind.Exception, "Sys.CharIndex");
buildContext.TryTakeException(toSearch.Exception, "Sys.CharIndex");
return sql;
}));
}
else
{
return(new SysFn((buildContext, buildArgs) =>
{
var sql = String.Format(
"CHARINDEX({0},{1},{2})",
toFind.Build(buildContext, buildArgs),
toSearch.Build(buildContext, buildArgs),
startLocation.Build(buildContext, buildArgs));
buildContext.TryTakeException(toFind.Exception, "Sys.CharIndex");
buildContext.TryTakeException(toSearch.Exception, "Sys.CharIndex");
buildContext.TryTakeException(startLocation.Exception, "Sys.CharIndex");
return sql;
}));
}
}
internal Expression(Operator op, ScalarArgument arg)
: this(null, true)
{
arg = arg ?? Designer.Null;
Arguments = new[] { arg };
TryStoreDbColumn(arg);
TryTake(arg);
Build = (buildContext, buildArgs) =>
{
if (buildContext.TryTakeException(chainException))
{
return(null);
}
if (!CheckConcatenator(buildContext, arg))
{
return(null);
}
arg.RootSubject = ((ISemantic)this).RootSubject;
var sql = BuildUnaryExpression(
op,
arg.Build(buildContext, buildArgs));
buildContext.TryTakeException(chainException);
return(sql);
};
}
/// <summary>
/// <para>STUFF built-in function.</para>
/// <para>The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.</para>
/// </summary>
/// <param name="argument">Is an expression of character data. textual can be a constant, variable, or column of either character or binary data.</param>
/// <param name="start">Is an integer value that specifies the location to start deletion and insertion.</param>
/// <param name="length">Is an integer that specifies the number of characters to delete.</param>
/// <param name="replaceWith">Is an expression of character data. replaceWith can be a constant, variable, or column of either character or binary data.</param>
public static SysFn Stuff(ScalarArgument argument, ScalarArgument start, ScalarArgument length, ScalarArgument replaceWith)
{
argument = argument ?? Designer.Null;
start = start ?? Designer.Null;
length = length ?? Designer.Null;
if (replaceWith == null)
{
replaceWith = Designer.Null;
}
return(new SysFn((buildContext, buildArgs) =>
{
var sql = String.Format(
"STUFF({0},{1},{2},{3})",
argument.Build(buildContext, buildArgs),
start.Build(buildContext, buildArgs),
length.Build(buildContext, buildArgs),
replaceWith.Build(buildContext, buildArgs));
buildContext.TryTakeException(argument.Exception, "Sys.Stuff");
buildContext.TryTakeException(start.Exception, "Sys.Stuff");
buildContext.TryTakeException(length.Exception, "Sys.Stuff");
buildContext.TryTakeException(replaceWith.Exception, "Sys.Stuff");
return sql;
}));
}
/// <summary>
/// <para>UPPER built-in function.</para>
/// <para>Returns a character expression with lowercase character data converted to uppercase.</para>
/// </summary>
/// <param name="argument">Is an expression of character data. textual can be a constant, variable, or column of either character or binary data.</param>
public static SysFn Upper(ScalarArgument argument)
{
argument = argument ?? Designer.Null;
return(new SysFn((buildContext, buildArgs) =>
{
var sql = String.Format(
"UPPER({0})",
argument.Build(buildContext, buildArgs));
buildContext.TryTakeException(argument.Exception, "Sys.Upper");
return sql;
}));
}
/// <summary>
/// <para>YEAR built-in function.</para>
/// <para>Returns an integer that represents the year of the specified date.</para>
/// </summary>
/// <param name="date">Is an expression that can be resolved to a time, date, smalldatetime, datetime, datetime2, or datetimeoffset value. The date argument can be an expression, column expression, user-defined variable or string literal.</param>
public static SysFn Year(ScalarArgument date)
{
date = date ?? Designer.Null;
return(new SysFn((buildContext, buildArgs) =>
{
var sql = String.Format(
"YEAR({0})",
date.Build(buildContext, buildArgs));
buildContext.TryTakeException(date.Exception, "Sys.Year");
return sql;
}));
}
/// <summary>
/// <para>BINARY_CHECKSUM built-in function.</para>
/// <para>Returns the binary checksum value computed over a row of a table or over a list of expressions. BINARY_CHECKSUM can be used to detect changes to a row of a table.</para>
/// </summary>
/// <param name="argument">Is an expression of any type. BINARY_CHECKSUM ignores expressions of noncomparable data types in its computation.</param>
public static SysFn BinaryChecksum(ScalarArgument argument)
{
argument = argument ?? Designer.Null;
return(new SysFn((buildContext, buildArgs) =>
{
var sql = String.Format(
"BINARY_CHECKSUM({0})",
argument.Build(buildContext, buildArgs));
buildContext.TryTakeException(argument.Exception, "Sys.BinaryChecksum");
return sql;
}));
}
/// <summary>
/// <para>ISNUMERIC built-in function.</para>
/// <para>Determines whether an expression is a valid numeric type.</para>
/// </summary>
/// <param name="argument">Is the expression to be evaluated.</param>
public static SysFn IsNumeric(ScalarArgument argument)
{
argument = argument ?? Designer.Null;
return(new SysFn((buildContext, buildArgs) =>
{
var sql = String.Format(
"ISNUMERIC({0})",
argument.Build(buildContext, buildArgs));
buildContext.TryTakeException(argument.Exception, "Sys.IsNumeric");
return sql;
}));
}
/// <summary>
/// <para>DATENAME built-in function.</para>
/// <para>Returns a character string that represents the specified datepart of the specified date.</para>
/// </summary>
/// <param name="datePart">Is the part of the date to return.</param>
/// <param name="date">Is an expression that can be resolved to a time, date, smalldatetime, datetime, datetime2, or datetimeoffset value. date can be an expression, column expression, user-defined variable, or string literal.</param>
public static SysFn Datename(Designer.PartOfDate datePart, ScalarArgument date)
{
date = date ?? Designer.Null;
return(new SysFn((buildContext, buildArgs) =>
{
var sql = String.Format(
"DATENAME({0},{1})",
datePart.ToUpperCase(),
date.Build(buildContext, buildArgs));
buildContext.TryTakeException(date.Exception, "Sys.Datename");
return sql;
}));
}
/// <summary>
/// <para>TODATETIMEOFFSET built-in function.</para>
/// <para>Returns a datetimeoffset value that is translated from a datetime2 expression.</para>
/// </summary>
/// <param name="date">Is an expression that resolves to a datetime2 value.</param>
/// <param name="timeZone">Is an expression that represents the time zone offset in minutes (if an integer), for example -120, or hours and minutes (if a string), for example ‘+13.00’. The range is +14 to -14 (in hours). The expression is interpreted in local time for the specified time_zone.</param>
public static SysFn ToDatetimeOffset(ScalarArgument date, string timeZone)
{
date = date ?? Designer.Null;
return(new SysFn((buildContext, buildArgs) =>
{
var sql = String.Format(
"TODATETIMEOFFSET({0},{1})",
date.Build(buildContext, buildArgs),
Mapping.Build(timeZone, Mapping.DefaultStringType));
buildContext.TryTakeException(date.Exception, "Sys.ToDatetimeOffset");
return sql;
}));
}
/// <summary>
/// <para>SWITCHOFFSET built-in function.</para>
/// <para>Returns a datetimeoffset value that is changed from the stored time zone offset to a specified new time zone offset.</para>
/// </summary>
/// <param name="dateTimeOffset">Is an expression that can be resolved to a datetimeoffset(n) value.</param>
/// <param name="timeZone">Is a character string in the format [+|-]TZH:TZM or a signed integer (of minutes) that represents the time zone offset, and is assumed to be daylight-saving aware and adjusted.</param>
public static SysFn SwitchOffset(ScalarArgument dateTimeOffset, string timeZone)
{
dateTimeOffset = dateTimeOffset ?? Designer.Null;
return(new SysFn((buildContext, buildArgs) =>
{
var sql = String.Format(
"SWITCHOFFSET({0},{1})",
dateTimeOffset.Build(buildContext, buildArgs),
Mapping.Build(timeZone, Mapping.DefaultStringType));
buildContext.TryTakeException(dateTimeOffset.Exception, "Sys.SwitchOffset");
return sql;
}));
}
internal Expression(Operator op, ScalarArgument arg1, ScalarArgument arg2)
: this(null, true)
{
arg1 = arg1 ?? Designer.Null;
arg2 = arg2 ?? Designer.Null;
if (Value.IsNull(arg2.Original))
{
if (op == Operator.Equal)
{
op = Operator.IsNull;
}
else if (op == Operator.NotEqual)
{
op = Operator.IsNotNull;
}
}
Arguments = new[] { arg1, arg2 };
TryStoreDbColumn(arg1);
TryTake(arg1);
TryTake(arg2);
Build = (buildContext, buildArgs) =>
{
if (buildContext.TryTakeException(chainException))
{
return(null);
}
if (!CheckConcatenator(buildContext, arg1) || !CheckConcatenator(buildContext, arg2))
{
return(null);
}
arg1.RootSubject = ((ISemantic)this).RootSubject;
arg2.RootSubject = ((ISemantic)this).RootSubject;
var sql = BuildBinaryExpression(
op,
arg1.Build(buildContext, buildArgs),
arg2.Build(buildContext, buildArgs));
buildContext.TryTakeException(chainException);
return(sql);
};
}
/// <summary>
/// <para>NULLIF built-in function.</para>
/// <para>Returns a null value if the two specified expressions are equal.</para>
/// </summary>
/// <param name="argument1">Is the first scalar expression.</param>
/// <param name="argument2">Is the second scalar expression.</param>
public static SysFn NullIf(ScalarArgument argument1, ScalarArgument argument2)
{
argument1 = argument1 ?? Designer.Null;
argument2 = argument2 ?? Designer.Null;
return(new SysFn((buildContext, buildArgs) =>
{
var sql = String.Format(
"NULLIF({0},{1})",
argument1.Build(buildContext, buildArgs),
argument2.Build(buildContext, buildArgs));
buildContext.TryTakeException(argument1.Exception, "Sys.NullIf");
buildContext.TryTakeException(argument2.Exception, "Sys.NullIf");
return sql;
}));
}
/// <summary>
/// <para>ISNULL built-in function.</para>
/// <para>Replaces NULL with the specified replacement value.</para>
/// </summary>
/// <param name="argument">Is the expression to be checked for NULL. The argument can be of any type.</param>
/// <param name="replacement">Is the expression to be returned if check expression is NULL. replacement value must be of a type that is implicitly convertible to the type of check expresssion.</param>
public static SysFn IsNull(ScalarArgument argument, ScalarArgument replacement)
{
argument = argument ?? Designer.Null;
replacement = replacement ?? Designer.Null;
return(new SysFn((buildContext, buildArgs) =>
{
var sql = String.Format(
"ISNULL({0},{1})",
argument.Build(buildContext, buildArgs),
replacement.Build(buildContext, buildArgs));
buildContext.TryTakeException(argument.Exception, "Sys.IsNull");
buildContext.TryTakeException(replacement.Exception, "Sys.IsNull");
return sql;
}));
}
/// <summary>
/// <para>PATINDEX built-in function.</para>
/// <para>Returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found, on all valid text and character data types.</para>
/// </summary>
/// <param name="pattern">Is a character expression that contains the sequence to be found. Wildcard characters can be used; however, the % character must come before and follow pattern (except when you search for first or last characters). pattern is an expression of the character string data type category. pattern is limited to 8000 characters.</param>
/// <param name="argument">Is an expression, typically a column that is searched for the specified pattern. argument is of the character string data type category.</param>
public static SysFn Patindex(ScalarArgument pattern, ScalarArgument argument)
{
pattern = pattern ?? Designer.Null;
argument = argument ?? Designer.Null;
return(new SysFn((buildContext, buildArgs) =>
{
var sql = String.Format(
"PATINDEX({0},{1})",
pattern.Build(buildContext, buildArgs),
argument.Build(buildContext, buildArgs));
buildContext.TryTakeException(pattern.Exception, "Sys.Patindex");
buildContext.TryTakeException(argument.Exception, "Sys.Patindex");
return sql;
}));
}
/// <summary>
/// <para>DATEDIFF built-in function.</para>
/// <para>Returns an integer that represents the specified datepart of the specified date.</para>
/// </summary>
/// <param name="datePart">Is the part of date (a date or time value) for which an integer will be returned.</param>
/// <param name="startDate">Is an expression that can be resolved to a time, date, smalldatetime, datetime, datetime2, or datetimeoffset value. date can be an expression, column expression, user-defined variable or string literal.</param>
/// <param name="endDate">Is an expression that can be resolved to a time, date, smalldatetime, datetime, datetime2, or datetimeoffset value. date can be an expression, column expression, user-defined variable or string literal.</param>
public static SysFn Datediff(Designer.PartOfDate datePart, ScalarArgument startDate, ScalarArgument endDate)
{
startDate = startDate ?? Designer.Null;
endDate = endDate ?? Designer.Null;
return(new SysFn((buildContext, buildArgs) =>
{
var sql = String.Format(
"DATEDIFF({0},{1},{2})",
datePart.ToUpperCase(),
startDate.Build(buildContext, buildArgs),
endDate.Build(buildContext, buildArgs));
buildContext.TryTakeException(startDate.Exception, "Sys.Datediff");
buildContext.TryTakeException(endDate.Exception, "Sys.Datediff");
return sql;
}));
}
/// <summary>
/// <para>DATEADD built-in function.</para>
/// <para>Returns a specified date with the specified number interval (signed integer) added to a specified datepart of that date.</para>
/// </summary>
/// <param name="datePart">Is the part of date to which an integer number is added. </param>
/// <param name="number">Is an expression that can be resolved to an int that is added to a datepart of date. User-defined variables are valid.</param>
/// <param name="date">Is an expression that can be resolved to a time, date, smalldatetime, datetime, datetime2, or datetimeoffset value. date can be an expression, column expression, user-defined variable, or string literal. If the expression is a string literal, it must resolve to a datetime.</param>
public static SysFn Dateadd(Designer.PartOfDate datePart, ScalarArgument number, ScalarArgument date)
{
number = number ?? Designer.Null;
date = date ?? Designer.Null;
return(new SysFn((buildContext, buildArgs) =>
{
var sql = String.Format(
"DATEADD({0},{1},{2})",
datePart.ToUpperCase(),
number.Build(buildContext, buildArgs),
date.Build(buildContext, buildArgs));
buildContext.TryTakeException(number.Exception, "Sys.Dateadd");
buildContext.TryTakeException(date.Exception, "Sys.Dateadd");
return sql;
}));
}
/// <summary>
/// <para>SUBSTRING built-in function.</para>
/// <para>Returns part of a character, binary, text, or image expression.</para>
/// </summary>
/// <param name="argument">Is a character, binary, text, ntext, or image expression.</param>
/// <param name="start">Is an integer or bigint expression that specifies where the returned characters start.</param>
/// <param name="length">Is a positive integer or bigint expression that specifies how many characters of the expression will be returned.</param>
public static SysFn Substring(ScalarArgument argument, ScalarArgument start, ScalarArgument length)
{
argument = argument ?? Designer.Null;
start = start ?? Designer.Null;
length = length ?? Designer.Null;
return(new SysFn((buildContext, buildArgs) =>
{
var sql = String.Format(
"SUBSTRING({0},{1},{2})",
argument.Build(buildContext, buildArgs),
start.Build(buildContext, buildArgs),
length.Build(buildContext, buildArgs));
buildContext.TryTakeException(argument.Exception, "Sys.Substring");
buildContext.TryTakeException(start.Exception, "Sys.Substring");
buildContext.TryTakeException(length.Exception, "Sys.Substring");
return sql;
}));
}
/// <summary>
/// <para>STR built-in function.</para>
/// <para>Returns character data converted from numeric data.</para>
/// </summary>
/// <param name="argument">Is an expression of approximate numeric (float) data type with a decimal point.</param>
/// <param name="length">Is the total length. This includes decimal point, sign, digits, and spaces.</param>
/// <param name="decimalPrecision">Is the number of places to the right of the decimal point. decimalPrecision must be less than or equal to 16.</param>
public static SysFn Str(ScalarArgument argument, ScalarArgument length, ScalarArgument decimalPrecision)
{
argument = argument ?? Designer.Null;
length = length ?? Designer.Null;
decimalPrecision = decimalPrecision ?? Designer.Null;
return(new SysFn((buildContext, buildArgs) =>
{
var sql = String.Format(
"STR({0},{1},{2})",
argument.Build(buildContext, buildArgs),
length.Build(buildContext, buildArgs),
decimalPrecision.Build(buildContext, buildArgs));
buildContext.TryTakeException(argument.Exception, "Sys.Str");
buildContext.TryTakeException(length.Exception, "Sys.Str");
buildContext.TryTakeException(decimalPrecision.Exception, "Sys.Str");
return sql;
}));
}
internal Expression(Operator op, ScalarArgument arg1, ScalarArgument arg2, ScalarArgument arg3)
: this(null, true)
{
arg1 = arg1 ?? Designer.Null;
arg2 = arg2 ?? Designer.Null;
arg3 = arg3 ?? Designer.Null;
Arguments = new[] { arg1, arg2, arg3 };
TryStoreDbColumn(arg1);
TryTake(arg1);
TryTake(arg2);
TryTake(arg3);
Build = (buildContext, buildArgs) =>
{
if (buildContext.TryTakeException(chainException))
{
return(null);
}
if (!CheckConcatenator(buildContext, arg1) || !CheckConcatenator(buildContext, arg2) || !CheckConcatenator(buildContext, arg3))
{
return(null);
}
arg1.RootSubject = ((ISemantic)this).RootSubject;
arg2.RootSubject = ((ISemantic)this).RootSubject;
arg3.RootSubject = ((ISemantic)this).RootSubject;
var sql = BuildThreePartExpression(buildContext,
op,
arg1.Build(buildContext, buildArgs),
arg2.Build(buildContext, buildArgs),
arg3.Build(buildContext, buildArgs));
buildContext.TryTakeException(chainException);
return(sql);
};
}
/// <summary>
/// <para>REPLACE built-in function.</para>
/// <para>Replaces all occurrences of a specified string value with another string value.</para>
/// </summary>
/// <param name="toSearch">Is the string expression to be searched. toSearch can be of a character or binary data type.</param>
/// <param name="pattern">Is the substring to be found. pattern can be of a character or binary data type.</param>
/// <param name="replaceWith">Is the replacement string. replaceWith can be of a character or binary data type.</param>
public static SysFn Replace(
ScalarArgument toSearch, ScalarArgument pattern, ScalarArgument replaceWith)
{
toSearch = toSearch ?? Designer.Null;
pattern = pattern ?? Designer.Null;
replaceWith = replaceWith ?? Designer.Null;
return(new SysFn((buildContext, buildArgs) =>
{
var sql = String.Format(
"REPLACE({0},{1},{2})",
toSearch.Build(buildContext, buildArgs),
pattern.Build(buildContext, buildArgs),
replaceWith.Build(buildContext, buildArgs));
buildContext.TryTakeException(toSearch.Exception, "Sys.Replace");
buildContext.TryTakeException(pattern.Exception, "Sys.Replace");
buildContext.TryTakeException(replaceWith.Exception, "Sys.Replace");
return sql;
}));
}
