Convert › Excel → T-SQL

Excel to T-SQL translation hub

Paste a formula on the function’s page. JavaScript writes the T-SQL — JOIN, CASE, STRING_AGG. 39 pages. Nothing is uploaded.

39 functions

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().

Then write it on a ticket

Monday is free. A colleague asks, you type, we mark it.