| Aggregation |
AVG(X) |
`AVG(X)` |
|
| Aggregation |
COUNT(X) |
`COUNT(X)` or `COUNT(*)` |
`COUNT(X)` counts non-NULL values in column X. `COUNT(*)` counts all rows. |
| Aggregation |
COUNT_DISTINCT(X) |
`COUNT(DISTINCT X)` |
|
| Aggregation |
MAX(X) |
`MAX(X)` |
|
| Aggregation |
MEDIAN(X) |
`PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY X) OVER ()` (SQL Server 2012+) |
Azure SQL Database supports `PERCENTILE_CONT` and `PERCENTILE_DISC` for calculating medians/percentiles. |
| Aggregation |
MIN(X) |
`MIN(X)` |
|
| Aggregation |
PERCENTILE(X,N) |
`PERCENTILE_CONT(N/100) WITHIN GROUP (ORDER BY X) OVER ()` or `PERCENTILE_DISC(N/100) WITHIN GROUP (ORDER BY X) OVER ()` |
`N` should be between 0 and 100. `PERCENTILE_CONT` (continuous distribution) interpolates, `PERCENTILE_DISC` (discrete distribution) returns an actual value from the set. |
| Aggregation |
STDDEV(X) |
`STDEV(X)` (sample) or `STDEVP(X)` (population) |
|
| Aggregation |
SUM(X) |
`SUM(X)` |
|
| Aggregation |
VARIANCE(X) |
`VAR(X)` (sample) or `VARP(X)` (population) |
|
| Arithmetic |
ABS(X) |
`ABS(X)` |
|
| Arithmetic |
ACOS(X) |
`ACOS(X)` |
|
| Arithmetic |
ASIN(X) |
`ASIN(X)` |
|
| Arithmetic |
ATAN(X) |
`ATAN(X)` |
|
| Arithmetic |
CEIL(X) |
`CEILING(X)` |
|
| Arithmetic |
COS(X) |
`COS(X)` |
|
| Arithmetic |
FLOOR(X) |
`FLOOR(X)` |
|
| Arithmetic |
LOG(X) (base 2) |
`LOG(X, 2)` |
SQL Server's `LOG` function allows specifying the base. |
| Arithmetic |
LOG10(X) |
`LOG10(X)` |
|
| Arithmetic |
NARY_MAX(X, Y [,Z]*) |
`IIF(X > Y, X, Y)` (for two arguments), nested `IIF` or `CASE` for more. |
SQL Server 2012+ supports `IIF`. For multiple arguments, you can nest `IIF` or use a `CASE` statement. |
| Arithmetic |
NARY_MIN(X, Y [,Z]*) |
`IIF(X < Y, X, Y)` (for two arguments), nested `IIF` or `CASE` for more. |
Similar to `NARY_MAX`, use `IIF` or `CASE` for multiple arguments. |
| Arithmetic |
POWER(X, Y) |
`POWER(X, Y)` |
|
| Arithmetic |
ROUND(X, Y) |
`ROUND(X, Y)` |
|
| Arithmetic |
SIN(X) |
`SIN(X)` |
|
| Arithmetic |
SQRT(X) |
`SQRT(X)` |
|
| Arithmetic |
TAN(X) |
`TAN(X)` |
|
| Conditional |
CASE WHEN condition THEN result [ELSE else_result] END |
`CASE WHEN condition THEN result [WHEN condition THEN result] [...] [ELSE else_result] END` |
Standard SQL `CASE` statement. |
| Conditional |
CASE input_expression WHEN expr_to_match THEN result [ELSE result] END |
`CASE input_expression WHEN expression_to_match THEN result [WHEN expression_to_match THEN result] [...] [ELSE result] END` |
Standard SQL `CASE` (simple form). |
| Conditional |
COALESCE(field_expression[,field_expression, ...]) |
`COALESCE(field_expression1, field_expression2, ...)` |
|
| Conditional |
IF(condition, true_result, false_result) |
`IIF(condition, true_result, false_result)` (SQL Server 2012+) or `CASE WHEN condition THEN true_result ELSE false_result END` |
`IIF` is a more concise way to write simple `CASE` statements. |
| Conditional |
IFNULL(input_expression, null_result) |
`ISNULL(input_expression, null_result)` or `COALESCE(input_expression, null_result)` |
`ISNULL` is SQL Server specific. `COALESCE` is standard SQL. |
| Conditional |
NULLIF(input_expression, expression_to_match) |
`NULLIF(input_expression, expression_to_match)` |
|
| Date |
CURRENT_DATE([time_zone]) |
`CAST(GETDATE() AS DATE)` or `CURRENT_TIMESTAMP` (then cast) |
`GETDATE()` returns current datetime. Cast to `DATE` for only the date part. Time zone handling is more complex in SQL. |
| Date |
CURRENT_DATETIME([time_zone]) |
`GETDATE()` or `SYSDATETIME()` |
`SYSDATETIME()` provides higher precision than `GETDATE()`. For time zones, consider `GETUTCDATE()` or `SYSDATETIMEOFFSET()`. |
| Date |
DATE(year, month, day) |
`DATEFROMPARTS(year, month, day)` |
SQL Server 2012+ function. |
| Date |
DATE_DIFF(X, Y) (days) |
`DATEDIFF(day, Y, X)` |
Calculates the difference in specified datepart (`day`). |
| Date |
DATE_FROM_UNIX_DATE(integer) |
`DATEADD(day, integer, '1970-01-01')` |
Adds the integer number of days to the Unix epoch. |
| Date |
DATETIME(year, month, day, hour, minute, second) |
`DATETIMEFROMPARTS(year, month, day, hour, minute, second, 0)` |
SQL Server 2012+ function. The last argument is milliseconds (set to 0). |
| Date |
DATETIME_ADD(datetime_expression, INTERVAL integer part) |
`DATEADD(part, integer, datetime_expression)` |
`part` can be `year`, `quarter`, `month`, `dayofyear`, `day`, `week`, `weekday`, `hour`, `minute`, `second`, `millisecond`. |
| Date |
DATETIME_DIFF(date_expression, date_expression, part) |
`DATEDIFF(part, date_expression1, date_expression2)` |
Calculates the difference in specified `part`. Note the order of arguments. |
| Date |
DATETIME_SUB(datetime_expression, INTERVAL integer part) |
`DATEADD(part, -integer, datetime_expression)` |
Subtracts the specified interval by using a negative integer. |
| Date |
DATETIME_TRUNC(date_expression, part) |
`DATEADD(part, DATEDIFF(part, 0, date_expression), 0)` |
Truncates a date to the beginning of the specified `part`. E.g., `DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)` for start of month. |
| Date |
DAY(date_expression) |
`DAY(date_expression)` |
|
| Date |
EXTRACT(part FROM date_expression) |
`DATEPART(part, date_expression)` |
`part` can be `year`, `month`, `day`, `hour`, `minute`, `second`, `quarter`, `week`, `weekday`, etc. |
| Date |
FORMAT_DATETIMEReturns a formatted date string. |
`FORMAT(datetime_expression, format_string)` (SQL Server 2012+) or `CONVERT(VARCHAR, datetime_expression, style)` |
`FORMAT` is more flexible for custom formats. `CONVERT` uses predefined styles. |
| Date |
HOUR(datetime_expression) |
`DATEPART(hour, datetime_expression)` or `HOUR(datetime_expression)` (deprecated for `DATEPART`) |
`DATEPART` is the preferred function. |
| Date |
MINUTE(datetime_expression) |
`DATEPART(minute, datetime_expression)` or `MINUTE(datetime_expression)` (deprecated) |
`DATEPART` is the preferred function. |
| Date |
MONTH(date_expression) |
`MONTH(date_expression)` |
|
| Date |
PARSE_DATE(format_string, text) |
`CONVERT(DATE, text, style)` or `TRY_CONVERT(DATE, text, style)` |
`style` is a numeric code for date format. `TRY_CONVERT` returns NULL on failure. |
| Date |
PARSE_DATETIME(format_string, text) |
`CONVERT(DATETIME, text, style)` or `TRY_CONVERT(DATETIME, text, style)` |
Similar to `PARSE_DATE` but for datetime. |
| Date |
QUARTER(date_expression) |
`DATEPART(quarter, date_expression)` |
|
| Date |
SECOND(datetime_expression) |
`DATEPART(second, datetime_expression)` or `SECOND(datetime_expression)` (deprecated) |
`DATEPART` is the preferred function. |
| Date |
TODATE(X, Input Format, Output Format) |
`CONVERT(DATE, X, style)` or `FORMAT(X, 'yyyy-MM-dd')` |
Conversion to `DATE` type. `FORMAT` can be used for string representation. |
| Date |
TODAY([time_zone]) |
`CAST(GETDATE() AS DATE)` |
|
| Date |
UNIX_DATE(date_expression) |
`DATEDIFF(day, '1970-01-01', date_expression)` |
Calculates the number of days since the Unix epoch. |
| Date |
WEEK(Date) |
`DATEPART(week, Date)` or `DATEPART(iso_week, Date)` |
`iso_week` is for ISO 8601 week number. |
| Date |
WEEKDAY(Date) |
`DATEPART(weekday, Date)` |
Returns a number (1-7) representing the day of the week, dependent on `SET DATEFIRST` setting. |
| Date |
YEAR(Date) |
`YEAR(Date)` |
|
| Date |
YEARWEEK(Date) |
`CONCAT(YEAR(Date), FORMAT(DATEPART(week, Date), '00'))` |
Concatenates year and two-digit week number. `DATEPART(iso_week, Date)` can be used for ISO week. |
| Geo |
TOCITY(X [,Input Format]) |
Requires custom lookup table or external service. |
Azure SQL does not have built-in functions for direct geographical name lookups from arbitrary input. You would typically use a pre-populated lookup table (e.g., city/country codes) or integrate with an external geocoding API. |
| Geo |
TOCONTINENT(X [,Input Format]) |
Requires custom lookup table or external service. |
|
| Geo |
TOCOUNTRY(X [,Input Format]) |
Requires custom lookup table or external service. |
|
| Geo |
TOREGION(X [,Input Format]) |
Requires custom lookup table or external service. |
|
| Geo |
TOSUBCONTINENT(X [,Input Format]) |
Requires custom lookup table or external service. |
|
| Miscellaneous |
CAST(field_expression AS TYPE) |
`CAST(field_expression AS TYPE)` or `CONVERT(TYPE, field_expression)` |
`TYPE` can be `INT`, `DECIMAL`, `VARCHAR`, `NVARCHAR`, `DATE`, `DATETIME`, etc. `CAST` is standard SQL, `CONVERT` is SQL Server specific and offers more style options for dates/times. |
| Miscellaneous |
HYPERLINK(URL, link label) |
No direct SQL equivalent for display. |
SQL stores data. The rendering of hyperlinks is typically handled by the client application (e.g., a reporting tool, web page). You would store the URL as a `VARCHAR` or `NVARCHAR`. |
| Miscellaneous |
IMAGE(Image URL, [Alternative Text]) |
No direct SQL equivalent for display. |
SQL stores the image URL as a `VARCHAR` or `NVARCHAR`. The display is handled by the client application. |
| Miscellaneous |
NATIVE_DIMENSION("JSON_VALUE('{"name": "Dana"}', '$.name')","STRING") |
`JSON_VALUE(expression, path)` (SQL Server 2016+) |
Azure SQL supports JSON functions. The example shows extracting a value from a JSON string. |
| Text |
CONCAT(X, Y) |
`CONCAT(X, Y)` (SQL Server 2012+) or `X + Y` |
`CONCAT` handles NULLs more gracefully (treats NULL as empty string). `+` operator will result in NULL if any part is NULL. |
| Text |
CONTAINS_TEXT(X, text) (case-sensitive) |
`CHARINDEX(text, X) > 0` |
`CHARINDEX` is case-sensitive by default depending on database collation. Use `COLLATE` for explicit control. |
| Text |
ENDS_WITH(X, text) (case-sensitive) |
`RIGHT(X, LEN(text)) = text` |
|
| Text |
LEFT_TEXT(X, length) |
`LEFT(X, length)` |
|
| Text |
LENGTH(X) |
`LEN(X)` |
|
| Text |
LOWER(X) |
`LOWER(X)` |
|
| Text |
REGEXP_CONTAINS(X, regular_expression) |
`X LIKE '%pattern%'` (for simple patterns), or CLR functions for full regex. |
Azure SQL (SQL Server) does not have native regular expression functions like some other SQL dialects. `LIKE` is for basic wildcard matching. Full regex requires CLR integration, which might have limitations in Azure SQL DB. |
| Text |
REGEXP_EXTRACT(X, regular_expression) |
Requires custom parsing with `SUBSTRING`, `CHARINDEX`, or CLR functions. |
|
| Text |
REGEXP_MATCH(X, regular_expression) |
`X LIKE 'pattern'` (for simple patterns), or CLR functions for full regex. |
|
| Text |
REGEXP_REPLACE(X, regular_expression, replacement) |
`REPLACE(X, 'old_string', 'new_string')` or custom parsing/CLR. |
`REPLACE` is for exact string replacement. Regex replacement is not native. |
| Text |
REPLACE(X, Y, Z) |
`REPLACE(X, Y, Z)` |
|
| Text |
RIGHT_TEXT(X, length) |
`RIGHT(X, length)` |
|
| Text |
STARTS_WITH(X, text) (case-sensitive) |
`X LIKE 'text%'` |
Case-sensitivity depends on database collation. Use `COLLATE` for explicit control. |
| Text |
SUBSTR(X, start index, length) |
`SUBSTRING(X, start index, length)` |
`start index` is 1-based. |
| Text |
TRIM(X) |
`TRIM(X)` (SQL Server 2017+) or `LTRIM(RTRIM(X))` |
`TRIM` removes spaces from both ends. `LTRIM(RTRIM(X))` is a common older method. |
| Text |
UPPER(X) |
`UPPER(X)` |
|
Comments
Post a Comment