Google data studio function corresponding Azure SQL formulas

Category Original Function Azure SQL Equivalent Notes 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)