Tools › Convert
Excel, Pandas, and SQL dialect converters
One URL per function, method, or dialect. Paste in the browser. We do not upload it. Then write the same idea on a Harbour & Co ticket.
75 converters
Excel → T-SQL
VLOOKUP is a JOIN. IF is CASE. 39 function pages.
VLOOKUPThe lookup column is a JOIN key, not a range.XLOOKUPXLOOKUP is VLOOKUP that can look left. In SQL that is still a JOIN.IFIF(test, a, b) is CASE WHEN test THEN a ELSE b END.IFERRORExcel swallows #N/A. SQL uses TRY_CONVERT or a LEFT JOIN.IFNAIFNA is IFERROR for #N/A only. In SQL that is a missed JOIN.IFSIFS(t1,a,t2,b) is CASE with two WHENs.COUNTIFCOUNTIF(range, crit) is SUM(CASE WHEN crit THEN 1 ELSE 0 END) or a filtered COUNT(*).COUNTIFSExtra criteria are extra ANDs, not extra functions.SUMIFSUMIF(range, crit, sum_range) is SUM(CASE WHEN crit THEN amount END).SUMIFSSUMIFS adds ANDs. The sum column comes first in Excel; in SQL it is just SUM(col).AVERAGEIFAVERAGEIF is AVG with a WHERE, or AVG(CASE…).SUMPRODUCTSUMPRODUCT is SUM(a * b), often with boolean tests coerced to 1/0.CONCATENATECONCATENATE(a,b) is CONCAT(a, b). Plus dies on NULL.CONCATSame function name. Watch NULL and the separator.TEXTJOINTEXTJOIN(delim, ignore_empty, range) is STRING_AGG with a WHERE.LEFTLEFT(text, n) is LEFT(col, n). Same word.RIGHTRIGHT(text, n) is RIGHT(col, n).MIDMID(text, start, n) is SUBSTRING(col, start, n).LENLEN(text) is LEN(col). Trailing spaces are ignored in T-SQL LEN.TRIMTRIM is TRIM on 2017+, LTRIM(RTRIM()) on older boxes.UPPERUPPER(text) is UPPER(col).LOWERLOWER(text) is LOWER(col).PROPERSQL Server has no PROPER. You write it, or you do it in the app.FINDFIND is case-sensitive. CHARINDEX follows collation.SEARCHSEARCH is case-insensitive FIND. CHARINDEX plus a CI collation.SUBSTITUTESUBSTITUTE(text, old, new) is REPLACE(col, old, new).TEXTTEXT(d,"yyyy-mm-dd") is CONVERT(varchar, d, 23) or FORMAT.VALUEVALUE(text) is TRY_CONVERT(decimal, col). Junk becomes NULL, not #VALUE!.DATEDATE(y,m,d) is DATEFROMPARTS(y, m, d).EOMONTHSame name. EOMONTH(date, 0) is month end.NETWORKDAYSSQL Server has no NETWORKDAYS. Count weekdays from a calendar table.INDEX/MATCHINDEX/MATCH is a JOIN. MATCH is the ON, INDEX is the column you select.UNIQUEUNIQUE(range) is SELECT DISTINCT or GROUP BY.FILTERFILTER(range, test) is SELECT … WHERE test.MINIFSMINIFS is MIN plus AND filters.MAXIFSMAXIFS is MAX plus AND filters.ROUNDROUND(n, d) is ROUND(col, d). Banker’s rounding can differ.TODAYTODAY() is the date. GETDATE() has a time. Cast it.NOWNOW() is GETDATE() or SYSDATETIME().
Pandas → T-SQL
merge is JOIN. groupby is GROUP BY. fillna is ISNULL. 28 method pages.
mergehow="left" is LEFT JOIN. on= is the ON clause.joinjoin aligns on the index. In SQL the index is just another key.concatStacking frames is UNION ALL. Aligning columns by name is the SELECT list.groupbygroupby("col").agg(...) is GROUP BY col plus aggregates.aggagg(["sum","mean"]) is SUM and AVG in the same SELECT.pivotindex / columns / values is the PIVOT shape.pivot_tablepivot_table is GROUP BY plus an optional PIVOT. aggfunc=sum is SUM.fillnafillna(0) is ISNULL(col, 0). fillna(method="ffill") is harder.dropnadropna() is WHERE col IS NOT NULL. how="any" is several ANDs.drop_duplicateskeep="first" is ROW_NUMBER = 1. A unique list is DISTINCT.locloc[mask, cols] is SELECT cols WHERE mask.ilociloc[:10] is TOP 10. iloc[10:20] is OFFSET 10 FETCH NEXT 10.queryquery("a > 1 and b == 'x'") is WHERE a > 1 AND b = 'x'.sort_valuesascending=False is DESC. Two columns are two ORDER BY keys.renamerename(columns={"a":"b"}) is SELECT a AS b.astypeastype("int64") is CAST or TRY_CONVERT. Dirty text needs TRY.applyapply(lambda r: ...) is usually a CASE. Rarely a function.value_countsvalue_counts() is GROUP BY col, COUNT(*), ORDER BY n DESC.nuniquenunique() is COUNT(DISTINCT col).headhead(n) is SELECT TOP n. You still need ORDER BY.tailtail(n) is TOP n with the opposite ORDER BY, or OFFSET to the end.reset_indexreset_index() turns an index into a column. SQL columns already are.assignassign(x=...) is a computed column in the SELECT.wherewhere(cond, other) keeps the value when cond, else other. That is CASE.replacereplace("a","b") on a string is REPLACE(). A dict of values is CASE.str.containsstr.contains("x") is LIKE '%x%'. regex=True is PATINDEX or a real CLR — prefer LIKE.dt.to_periodto_period("M") is CONVERT(char(7), d, 120). to_period("Q") is year plus quarter.to_sqlto_sql writes rows. In SQL Server that is a staging table plus INSERT or BULK INSERT.
SQL dialects
LIMIT, IFNULL, NVL, QUALIFY — rewritten for SQL Server, and two the other way.
MySQL → T-SQLLIMIT, IFNULL, NOW, GROUP_CONCAT → TOP, ISNULL, GETDATE, STRING_AGG.PostgreSQL → T-SQLLIMIT, ILIKE, ::type, ||, FILTER, DATE_TRUNC → T-SQL.Oracle → T-SQLNVL, DECODE, SYSDATE, ROWNUM, VARCHAR2, LISTAGG → T-SQL.Snowflake → T-SQLQUALIFY, VARIANT, ILIKE, LIMIT, LISTAGG → T-SQL windows and OPENJSON.SQLite → T-SQLLIMIT, IFNULL, ||, date('now'), AUTOINCREMENT → T-SQL.BigQuery → T-SQLLIMIT, IFNULL, ||, SAFE_CAST, QUALIFY, backticks → T-SQL.T-SQL → MySQLTOP, ISNULL, GETDATE, STRING_AGG → LIMIT, IFNULL, NOW, GROUP_CONCAT.T-SQL → PostgreSQLTOP, ISNULL, GETDATE, + concat → LIMIT, COALESCE, now(), ||.
Interview prompts
Top N per group, running totals, anti-joins. Draft in the box. Reveal a worked answer.
Then write it on a ticket
Monday is free. A colleague asks, you type, we mark it.
Clock in free