SQL DELIMITER explained

What is DELIMITER?

The DELIMITER operation changes the delimiter from the standard semicolon “;” to another type of delimiter.

When you define a functions, stored procedures, or triggers which wrap multiple statements inside, you must use a delimiter other than the default delimiter “;”, such as “\\’, or “$$’. Each statement inside the function still ends with “;”, while the function only ends with the custom defined delimiter.

By doing that, the SQL client knows where to terminate the function as a whole instead of terminating each body statement at a time.

Example

Define a custom function and use DELIMITER to define “\\”” as the the terminator of the function instead of the standard semi colon “;”

DELIMITER //

CREATE FUNCTION FortyTwo() RETURNS TINYINT DETERMINISTIC
BEGIN
  DECLARE x TINYINT;
  SET x = 42;
  RETURN x;
END 

//

DELIMITER ;

---

Need a good GUI tool for databases? TablePlus provides a native client that allows you to access and manage Oracle, MySQL, SQL Server, PostgreSQL and many other databases simultaneously using an intuitive and powerful graphical interface.

Download TablePlus for Mac.

Not on Mac? Download TablePlus for Windows.

Need a quick edit on the go? Download for iOS