# Values and special characters

## Values and special characters

In CODESYS special characters in strings must be encoded with `$` encoded.

#### Most important $ codes

| Character             | Code  | Usage         | Example                |
| --------------------- | ----- | ------------- | ---------------------- |
| `'` (Apostrophe)      | `$27` | Text in SQL   | `$27Max Mustermann$27` |
| `"` (Quotation marks) | `$22` | Rarely needed | `$22Text$22`           |
| `$` (Dollar)          | `$$`  | Dollar sign   | `$$100`                |

### Practical examples

#### Insert text values into SQL

```iecst
// Wrong - does not work:
MySQL_Command[0] := 'INSERT INTO users (name) VALUES ('Max')';

// Correct - with $27:
MySQL_Command[0] := 'INSERT INTO users (name) VALUES ($27Max$27)';
```

#### Dynamic text values

```iecst
VAR
    sName : STRING := 'Max Mustermann';
    sCity : STRING := 'Berlin';
END_VAR

// Enclose text with $27
MySQL_Command[0] := 'INSERT INTO users (name, city) VALUES ($27';
MySQL_Command[1] := CONCAT(sName, '$27, $27');
MySQL_Command[2] := CONCAT(sCity, '$27)');

// Result: INSERT INTO users (name, city) VALUES ('Max Mustermann', 'Berlin')
```

#### WHERE condition with text

```iecst
VAR
    sSearchName : STRING := 'Schmidt';
END_VAR

MySQL_Command[0] := 'SELECT * FROM users WHERE name = $27';
MySQL_Command[1] := CONCAT(sSearchName, '$27');

// Result: SELECT * FROM users WHERE name = 'Schmidt'
```

#### Date and time

```iecst
// Date
MySQL_Command[0] := 'SELECT * FROM logs WHERE date = $272024-01-15$27';

// Date and time
MySQL_Command[0] := 'SELECT * FROM logs WHERE timestamp = $272024-01-15 10:30:00$27';
```

#### Numbers do not need $27

```iecst
// Correct - numbers without $27:
MySQL_Command[0] := 'INSERT INTO data (value) VALUES (123)';

// Wrong - not for numbers:
MySQL_Command[0] := 'INSERT INTO data (value) VALUES ($27123$27)';  // WRONG!
```

#### NULL values

```iecst
// Correct - NULL without $27:
MySQL_Command[0] := 'INSERT INTO data (value) VALUES (NULL)';

// Wrong:
MySQL_Command[0] := 'INSERT INTO data (value) VALUES ($27NULL$27)';  // WRONG!
```

### Reference

#### Data type -> SQL format

| CoDeSys type | SQL format                  | Example code                |
| ------------ | --------------------------- | --------------------------- |
| **STRING**   | `$27text$27`                | `$27Berlin$27`              |
| **INT**      | Direct                      | `123`                       |
| **REAL**     | Direct (with dot)           | `23.5`                      |
| **BOOL**     | As 0/1 or text              | `1` or `$27TRUE$27`         |
| **DATE**     | `$27YYYY-MM-DD$27`          | `$272024-01-15$27`          |
| **TIME**     | `$27HH:MM:SS$27`            | `$2710:30:00$27`            |
| **DATETIME** | `$27YYYY-MM-DD HH:MM:SS$27` | `$272024-01-15 10:30:00$27` |

### Avoid common mistakes

#### Wrong

```iecst
MySQL_Command[0] := 'INSERT INTO users (name) VALUES ('Max')';  
// Syntax error!
```

#### Correct

```iecst
MySQL_Command[0] := 'INSERT INTO users (name) VALUES ($27Max$27)';
```

#### Wrong

```iecst
MySQL_Command[0] := 'INSERT INTO data (value) VALUES ($27100$27)';  
// Number as text!
```

#### Correct

```iecst
MySQL_Command[0] := 'INSERT INTO data (value) VALUES (100)';
```