MySQL vs. SQL Server: the same jobs, different functions

Trim a name, round up to whole cartons, count the days an order was late. MySQL and SQL Server both do these jobs, but often with different words, and a few look alike while doing different things.

By Gregor Dzierzon ·

>

Learn SQL on one database and you'll soon meet the other. A class teaches MySQL, and your first job runs SQL Server, or the other way around. The good news: SELECT, WHERE, JOIN and GROUP BY read the same in both. The surprises are in the built-in functions, the small tools that clean up text, round numbers and work with dates.

Here are the everyday jobs side by side, then the five differences most likely to trip you up.

Two workbenches side by side, labeled MySQL and SQL Server, each holding the same three tools, a magnifying glass, a calendar and a label maker, tagged with that database's name for the job: LOCATE and CHARINDEX, DATE_ADD and DATEADD, DATE_FORMAT and FORMAT

The same jobs, side by side

The job MySQL SQL Server (T-SQL)
Remove spaces at both ends TRIM(text) TRIM(text)
Swap one piece of text for another REPLACE(text, find, new) REPLACE(text, find, new)
Find where a piece of text is LOCATE(find, text) CHARINDEX(find, text)
How many characters CHAR_LENGTH(text) LEN(text)
Round up, round down CEILING, FLOOR CEILING, FLOOR
The date and time now NOW() GETDATE()
Days between two dates DATEDIFF(later, earlier) DATEDIFF(day, earlier, later)
A date 30 days later DATE_ADD(d, INTERVAL 30 DAY) DATEADD(day, 30, d)
A date as text for people DATE_FORMAT(d, '%M %e, %Y') FORMAT(d, 'MMMM d, yyyy')
A stand-in for NULL IFNULL(x, 'n/a') or COALESCE ISNULL(x, 'n/a') or COALESCE

Both databases count text positions from 1, not from 0 as Java and C# do: the space in Maria Anders is at position 6 in both. And some functions are spelled the same everywhere, COALESCE, NULLIF, UPPER, LOWER, ROUND and ABS among them.

Five differences that bite

1. DATEDIFF takes its dates in opposite orders. In MySQL the later date comes first and the answer is always in days: DATEDIFF('2026-03-18', '2026-03-16') is 2 (MySQL manual). SQL Server names the unit first, then the earlier date: DATEDIFF(day, '2026-03-16', '2026-03-18') is 2 (Microsoft Learn). Copy one into the other without swapping and every late order looks early.

2. CONCAT and NULL. Building a label from a city and a region, where the region is NULL:

-- MySQL: NULL, the whole label is gone
SELECT CONCAT('Berlin', ', ', NULL);

-- SQL Server: 'Berlin, ' (the NULL counts as empty text)
SELECT CONCAT('Berlin', ', ', NULL);

MySQL's CONCAT gives NULL if any part is NULL (MySQL manual); SQL Server's treats a NULL as empty text (Microsoft Learn). Both have CONCAT_WS(', ', city, region), which skips the NULLs and leaves no stray comma.

3. ISNULL means two different things. In SQL Server, ISNULL(region, 'n/a') supplies a stand-in (Microsoft Learn). In MySQL, ISNULL(region) takes one value and answers a question: 1 if it's NULL, 0 if it isn't (MySQL manual). COALESCE(region, 'n/a') works the same in both, so it's the one to reach for.

4. Dividing whole numbers. The warehouse packs 12 units to a carton, and a product has 39 in stock. In MySQL, 39 / 12 is 3.2500, so CEILING(39 / 12) is the 4 cartons you need (MySQL manual). In SQL Server, 39 / 12 is 3, because dividing two whole numbers drops the remainder (Microsoft Learn), so CEILING(39 / 12) is 3, one carton short. Write 39 / 12.0 and SQL Server gets 4 too.

5. Letter case in REPLACE. MySQL's REPLACE matches letter case exactly, so REPLACE('Chai', 'c', 'k') changes nothing (MySQL manual). SQL Server compares text by the database's collation, and the usual one ignores case, so the same call gives khai (Microsoft Learn).

A habit that travels

When a function is spelled the same in both, prefer it: COALESCE over IFNULL or ISNULL, CONCAT_WS when parts might be NULL. When it isn't, keep the other database's spelling in mind, and test the edge cases (a NULL, a 0, the last day of a month) on the database you're really using.

Go deeper

Each database gets a full lesson, with the problem each function solves, the Northwind sample data, a practice box and questions to check yourself:

On one page: the MySQL vs. SQL Server functions cheat sheet, to keep or to share.

Sources

Tags: mysql, sql, sql-server, t-sql

Glossary subjects