# Problem with SQL Query with special character in field and table name

**URL:** <https://discuss.elastic.co/t/problem-with-sql-query-with-special-character-in-field-and-table-name/146359>\
**Category:** Elasticsearch\
**Created:** [August 28, 2018, 1:40pm UTC](https://discuss.elastic.co/t/problem-with-sql-query-with-special-character-in-field-and-table-name/146359 "2018-08-28T13:40:19Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![stef35340](https://avatars.discourse-cdn.com/v4/letter/s/f05b48/32.png) [@stef35340](https://discuss.elastic.co/u/stef35340)\
**Post date:** [August 28, 2018, 1:40pm UTC](https://discuss.elastic.co/t/problem-with-sql-query-with-special-character-in-field-and-table-name/146359/1 "2018-08-28T13:40:20Z")

</div>

Hello,

I've an issue when I try to use fields and table names with some special characters.  
My indexes are named like the following xxxxxx-prod-2018.08.28 and the fact that there is a "dash" in the index name. As workaround, I searched like the following query: "SELECT \* FROM xxxxxx\*28 ORDER BY 1". Any trick to use tables with special characters in their name?

Another issue I have and I don't found the solution is when I want to use the @timestamp field: "SELECT @timestamp FROM xxxxxx\*28 ORDER BY 1", I obtain the following error:

{  
"error": {  
"root\_cause": [  
{  
"type": "parsing\_exception",  
"reason": "line 1:8: mismatched input '@timestamp' expecting {'(', 'ANALYZE', 'ANALYZED', 'CAST', 'CATALOGS', 'COLUMNS', 'DEBUG', 'EXECUTABLE', 'EXISTS', 'EXPLAIN', 'EXTRACT', 'FALSE', 'FORMAT', 'FUNCTIONS', 'GRAPHVIZ', 'LEFT', 'MAPPED', 'MATCH', 'NOT', 'NULL', 'OPTIMIZED', 'PARSED', 'PHYSICAL', 'PLAN', 'RIGHT', 'RLIKE', 'QUERY', 'SCHEMAS', 'SHOW', 'SYS', 'TABLES', 'TEXT', 'TRUE', 'TYPE', 'TYPES', 'VERIFY', '{FN', '{D', '{T', '{TS', '{GUID', '+', '-', '_', '?', STRING, INTEGER\_VALUE, DECIMAL\_VALUE, IDENTIFIER, DIGIT\_IDENTIFIER, QUOTED\_IDENTIFIER, BACKQUOTED\_IDENTIFIER}"  
}  
],  
"type": "parsing\_exception",  
"reason": "line 1:8: mismatched input '@timestamp' expecting {'(', 'ANALYZE', 'ANALYZED', 'CAST', 'CATALOGS', 'COLUMNS', 'DEBUG', 'EXECUTABLE', 'EXISTS', 'EXPLAIN', 'EXTRACT', 'FALSE', 'FORMAT', 'FUNCTIONS', 'GRAPHVIZ', 'LEFT', 'MAPPED', 'MATCH', 'NOT', 'NULL', 'OPTIMIZED', 'PARSED', 'PHYSICAL', 'PLAN', 'RIGHT', 'RLIKE', 'QUERY', 'SCHEMAS', 'SHOW', 'SYS', 'TABLES', 'TEXT', 'TRUE', 'TYPE', 'TYPES', 'VERIFY', '{FN', '{D', '{T', '{TS', '{GUID', '+', '-', '_', '?', STRING, INTEGER\_VALUE, DECIMAL\_VALUE, IDENTIFIER, DIGIT\_IDENTIFIER, QUOTED\_IDENTIFIER, BACKQUOTED\_IDENTIFIER}",  
"caused\_by": {  
"type": "input\_mismatch\_exception",  
"reason": null  
}  
},  
"status": 400  
}

Someone have any trick to use this field in SQL queries?

Thank you for you cooperation!

Stephane

---

<div class="post-metadata">

**Author:** ![Andrei\_Stefan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/andrei_stefan/32/47533_2.png) [@Andrei\_Stefan](https://discuss.elastic.co/u/Andrei_Stefan)\
**Post date:** [August 29, 2018, 5:25am UTC](https://discuss.elastic.co/t/problem-with-sql-query-with-special-character-in-field-and-table-name/146359/2 "2018-08-29T05:25:44Z")

</div>

Hi @stef35340,  
You simply need to put double-quotes for both the index name with a dash and the field name with `@`:

`select "@timestamp" from "xxx-123"`

---

<div class="post-metadata">

**Author:** ![NerdSec](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/nerdsec/32/22056_2.png) [@NerdSec](https://discuss.elastic.co/u/NerdSec)\
**Post date:** [August 29, 2018, 6:42am UTC](https://discuss.elastic.co/t/problem-with-sql-query-with-special-character-in-field-and-table-name/146359/3 "2018-08-29T06:42:17Z")

</div>

Hi Andrei,

This does not seem to work for me. I have an ELK 6.3.2 setup. Here is the query that I am trying to run on the Dev console.

```auto
POST _xpack/sql?format=txt
{
  "query": "select * from my-alarm"
}

```

The output i get is as follows:

```auto
{
  "error": {
    "root_cause": [
      {
        "type": "parsing_exception",
        "reason": "line 1:19: mismatched input '-' expecting {<EOF>, ',', 'FULL', 'GROUP', 'HAVING', 'INNER', 'JOIN', 'LEFT', 'LIMIT', 'NATURAL', 'ORDER', 'RIGHT', 'WHERE'}"
      }
    ],
    "type": "parsing_exception",
    "reason": "line 1:19: mismatched input '-' expecting {<EOF>, ',', 'FULL', 'GROUP', 'HAVING', 'INNER', 'JOIN', 'LEFT', 'LIMIT', 'NATURAL', 'ORDER', 'RIGHT', 'WHERE'}",
    "caused_by": {
      "type": "input_mismatch_exception",
      "reason": null
    }
  },
  "status": 400
}

```

I then tried to escape them using a single quote as escaping a double quote using a double quote simply terminated the query earlier than expected.

Here is the query:

```auto
POST _xpack/sql?format=txt
{
  "query": "select * from 'my-alarm'"
}

```

I received the following error:

```auto
{
  "error": {
    "root_cause": [
      {
        "type": "parsing_exception",
        "reason": "line 1:15: mismatched input ''my-alarm'' expecting {'(', 'ANALYZE', 'ANALYZED', 'CATALOGS', 'COLUMNS', 'DEBUG', 'EXECUTABLE', 'EXPLAIN', 'FORMAT', 'FUNCTIONS', 'GRAPHVIZ', 'MAPPED', 'OPTIMIZED', 'PARSED', 'PHYSICAL', 'PLAN', 'RLIKE', 'QUERY', 'SCHEMAS', 'SHOW', 'SYS', 'TABLES', 'TEXT', 'TYPE', 'TYPES', 'VERIFY', IDENTIFIER, DIGIT_IDENTIFIER, TABLE_IDENTIFIER, QUOTED_IDENTIFIER, BACKQUOTED_IDENTIFIER}"
      }
    ],
    "type": "parsing_exception",
    "reason": "line 1:15: mismatched input ''my-alarm'' expecting {'(', 'ANALYZE', 'ANALYZED', 'CATALOGS', 'COLUMNS', 'DEBUG', 'EXECUTABLE', 'EXPLAIN', 'FORMAT', 'FUNCTIONS', 'GRAPHVIZ', 'MAPPED', 'OPTIMIZED', 'PARSED', 'PHYSICAL', 'PLAN', 'RLIKE', 'QUERY', 'SCHEMAS', 'SHOW', 'SYS', 'TABLES', 'TEXT', 'TYPE', 'TYPES', 'VERIFY', IDENTIFIER, DIGIT_IDENTIFIER, TABLE_IDENTIFIER, QUOTED_IDENTIFIER, BACKQUOTED_IDENTIFIER}",
    "caused_by": {
      "type": "input_mismatch_exception",
      "reason": null
    }
  },
  "status": 400
}

```

This is a major issue as we create indices on a daily basis and they all dates are separated by '-'. Is it possible to query indices that have special characters?

Regards,  
Nachiket

---

<div class="post-metadata">

**Author:** ![NerdSec](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/nerdsec/32/22056_2.png) [@NerdSec](https://discuss.elastic.co/u/NerdSec)\
**Post date:** [August 29, 2018, 7:26am UTC](https://discuss.elastic.co/t/problem-with-sql-query-with-special-character-in-field-and-table-name/146359/4 "2018-08-29T07:26:32Z")

</div>

OK. Got it to work. Andrei was right, you will have to escape it. I ran this on the console as follows:

```auto
POST _xpack/sql
{
  "query": "select \"@timestamp\" from \"my-alarms-2018.08.28\""
}

```

Apparently, you had to escape the double quotes. 😄

---

<div class="post-metadata">

**Author:** ![stef35340](https://avatars.discourse-cdn.com/v4/letter/s/f05b48/32.png) [@stef35340](https://discuss.elastic.co/u/stef35340)\
**Post date:** [August 29, 2018, 7:59am UTC](https://discuss.elastic.co/t/problem-with-sql-query-with-special-character-in-field-and-table-name/146359/5 "2018-08-29T07:59:20Z")

</div>

So cool! Thanks NerdSec and Andrei\_Stefan! The solution is indeed to to escape double quotes before the name of the table or field with specific characters.

Have a nice day!

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [September 26, 2018, 7:59am UTC](https://discuss.elastic.co/t/problem-with-sql-query-with-special-character-in-field-and-table-name/146359/6 "2018-09-26T07:59:22Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.