end-delimiter SQL attribute

The end-delimiter attribute can be set in a sql or sqlFile Change Type to override the default value of ;. The end-delimiter can be set to '' or to a character other than ; to indicate the end of the SQL statement. The attribute is generally used to prevent incomplete SQL statements from being processed when the SQL files contain statements referencing a ; which Liquibase treats as the default end delimiter.

A corresponding rollback-end-delimiter applies specifically to rollback for sql and sqlFile Change Types.

The end-delimiter can also be set in the Liquibase properties file by setting delimiter=<string>.

Uses

The following examples demonstrate use of the end-delimiters SQL attribute:

Use end-delimiter in SQL for PROCEDURE and FUNCTION

An end-delimiter can be used when the changeset contains SQL to create a stored procedure or function that contains the default ';' end delimiter. To avoid incomplete statements being sent to the database, the changeset must be marked to have a different end-delimiter.

Use end-delimiter for some DBMS systems to run multiple statements

The end-delimiter must be specified for some dbms systems to run multiple statements.

Setting the end-delimiter attribute

Escaping an end-delimiter

When setting an end-delimiter, note that certain DBMS and operating systems require delimiter values to be escaped. For example, a $$ end-delimiter with mysql requires escaping as: end-delimiter="\$\$". A Liquibase migration error indicates the delimiter that needs to be escaped would be:

Unexpected error running Liquibase: Migration failed for change set <changelog>::<id>::<author>:
 Reason: liquibase.exception.DatabaseException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '$$$
 --liquibase formatted sql

--changeset your.name:101 end-delimiter:/
CREATE FUNCTION calcProfit2(cost FLOAT, price FLOAT) 
RETURNS DECIMAL(9,2)
DETERMINISTIC
BEGIN
  DECLARE profit DECIMAL(9,2);
  SET profit = price-cost;
  RETURN profit;
  END
  /

CREATE FUNCTION calcProfit3(cost FLOAT, price FLOAT) RETURNS DECIMAL(9,3)
DETERMINISTIC
BEGIN
DECLARE profit DECIMAL(9,3);
SET profit = price-cost;
RETURN profit;
END
/ 
<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
	xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
	xmlns:pro="http://www.liquibase.org/xml/ns/pro"
	xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
		http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.9.xsd
		http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd
		http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-4.9.xsd">
  <changeSet author="your.name" id="sql-example">
    <sql dbms="mysql" splitStatements="true" end-delimiter="/" stripComments="true">
      DROP FUNCTION IF EXISTS calcProfit;
      /
      CREATE FUNCTION calcProfit(cost FLOAT, price FLOAT) RETURNS DECIMAL(9,2)
      DETERMINISTIC
      BEGIN
        DECLARE profit DECIMAL(9,2);
        SET profit = price-cost;
        RETURN profit;
      END
      /
    </sql>
  </changeSet>
</databaseChangeLog>
databaseChangeLog:
 -  changeSet:
 id:  1001
 author:  your.name
 changes:
  -  sql:
     end-delimiter: "@@"
     splitStatements: true
     sql: DROP FUNCTION IF EXISTS calcProfit9; @@ CREATE FUNCTION calcProfit9(cost FLOAT, price FLOAT) RETURNS DECIMAL(9,2) DETERMINISTIC BEGIN DECLARE profit DECIMAL(9,2); SET profit = price-cost; RETURN profit; END @@
     stripComments: true
{
   "databaseChangeLog":[
      {
        "changeSet":{
            "id":"1001",
            "author":"your.name",
            "changes":[
               {
                  "sql":{
                    "end-delimiter":"@@",
                    "dbms":"mysql",
                    "splitStatements":true,
                    "sql":"DROP FUNCTION IF EXISTS calcProfit;\n@@\nCREATE FUNCTION calcProfit(cost FLOAT, price FLOAT) RETURNS DECIMAL(9,2)\nDETERMINISTIC\nBEGIN\nDECLARE profit DECIMAL(9,2);\nSET profit = price-cost;\nRETURN profit;\nEND\n@@",
  "stripComments":true
             }
           }
         ] 
       }
     }
   ]
 }