<a id="ksqldb-how-to-substitute-variables"></a>

# Variable Substitution in ksqlDB for Confluent Platform

## Context

You have a set of SQL statements, and you want to vary the exact content
depending on where you use them. To do that, ksqlDB supports *variables*
so that you can supply different values.

## In action

```sql
DEFINE format = 'AVRO';
DEFINE replicas = '3';

CREATE STREAM stream1 (
  id INT
) WITH (
  kafka_topic = 'stream1',
  value_format = '${format}',
  replicas = ${replicas}
);
```

## Use a variable

Begin by defining one or more variables with a
[compliant name](../developer-guide/ksqldb-reference/define.md#ksqldb-reference-define):

```sql
DEFINE format = 'AVRO';
DEFINE replicas = '3';
```

Now reference each variable by enclosing it between `${}` characters:

```sql
CREATE STREAM stream1 (
  id INT
) WITH (
  kafka_topic = 'stream1',
  value_format = '${format}',
  replicas = ${replicas}
);
```

#### NOTE
Variables are case-insensitive. A reference to `${replicas}` is the same
as `${REPLICAS}`.

At any point, you can list all variables to see what is available:

```sql
SHOW VARIABLES;
```

Which should output:

```text
 Variable Name | Value
----------------------------
 replicas      | 3
 format        | AVRO
----------------------------
```

If you want to undefine a variable, you can unbound the name like so:

```sql
UNDEFINE replicas;
```

#### NOTE
Single-quotes are removed during variable substitution. To escape
single-quotes, enclose the value with triple-quotes.

## Escape substitution variables

If you want to escape a variable reference so it is not substituted, use
double `$$` characters.

```sql
DEFINE format = 'AVRO';
SELECT '$${format}' FROM stream;
```

The above query will become `SELECT '${format}' FROM stream`.

## Context for substitution variables

Variable substitution is allowed in specific SQL statements. You can
replace text and non-text literals, and identifiers like column names
and stream/table names. You can’t use variables as reserved keywords.

The following statements show examples of using variables for stream and
column names, and in other places.

```sql
CREATE STREAM ${streamName} (
  ${colName1} INT,
  ${colName2} STRING
) WITH (
  kafka_topic = '${topicName}',
  format = '${format}',
  replicas = ${replicas},
  ...
);

INSERT INTO ${streamName} (
  ${colName1},
  ${colName2}
) VALUES (
  ${val1},
  '${val2}'
);

SELECT * FROM ${streamName} EMIT CHANGES;
```

Using a variable in a statement that doesn’t support variables causes a
SQL parsing error.

## Disable substitution variables

Enable or disable variable substitution by setting the
[ksql.variable.substitution.enable](../reference/server-configuration.md#ksqldb-server-config-variable-substitution-enable)
server configuration parameter.

```sql
ksql> SET 'ksql.variable.substitution.enable' = 'false';
ksql> CREATE STREAM ... WITH (kafka_topic='${topic_${env}}');
Error: Fail because ${topic_${env}} topic name is invalid.
```