How can i use sql like and clauses in ElasticSearch?

i read this document to understand equality of sql in elasticsearch.(https://taohiko.wordpress.com/2014/07/18/query-dsl-elasticsearch-vs-sql/) i developed a kinda elasticsearch application it is making indexes from my data if i call below post query by using postman :

{
  "query": {
    "multi_match" : {
      "query":      "TÜRKİYE iş 3124904300",
      "type":       "cross_fields",
      "fields":     [ "title", "tcknVkn","phone","townName","cityName","poiDesc","district","street","avenue","buildingName","addressName" ],
      "operator":   "and" 
    }
  }
}

it is working perfect. But i wanna do that;

{
  "query": {
    "multi_match" : {
      "query":      "TÜRKİYE iş 312*",
      "type":       "cross_fields",
      "fields":     [ "title", "tcknVkn","phone","townName","cityName","poiDesc","district","street","avenue","buildingName","addressName" ],
      "operator":   "and" 
    }
  }
}

means:

select * from mytable where title like 'TÜRKİYE%' and addressName like 'iş%' and 
tcknVkn like '312%'

but. if i write above query elasticsearch can not understand numerical value. So it returns me empty data? How can i solve this problem. How can i convert above sql query to elasticsearch query?

(BE CAREFUL : "title", "tcknVkn","phone","townName","cityName","poiDesc","district","street","avenue","buildingName","addressName" -> all fields are indexed as string )

multi_match query essentially create multiple match query. And your query create like following query:

{
  "query": {
    "bool": {
      "should": [
        { "match": { "title":   "TÜRKİYE iş 312*" }},
        { "match": { "tcknVkn": "TÜRKİYE iş 312*" }},
        { "match": { "phone":   "TÜRKİYE iş 312*" }},
        ...
      ]
    }
  }
}

It is not exactly like above example but similar. But you want to separate your query keywords. I create following example to exampling your situation:

POST test2/test/_mapping
{
  "properties": {
    "tcNo": {
      "type": "long",
      "fields": {
        "text": {
          "type": "text"
        },
        "numeric": {
          "type": "integer"
        }
      }
    }
  }
}

POST test2/test
{
  "id": 0,
  "tcNo": 23432344,
  "name": "hay0"
}

POST test2/test
{
  "id": 1,
  "tcNo": 23442344,
  "name": "haydar1"
}

POST test2/test
{
  "id": 2,
  "tcNo": 23432344,
  "name": "haydar2"
}

POST test2/test
{
  "id": 3,
  "tcNo": 23542344,
  "name": "haydar3"
}


GET test2/_search
{
  "query": {
    "multi_match" : {
      "query":      "haydar* 234*",
      "type":       "phrase_prefix",
      "fields":     ["tcNo.text", "name"],
      "operator":   "AND" 
    }
  }
}

POST test2/test/_search
{
  "query": {
    "query_string": {
      "fields": ["tcNo.text", "name"],
      "query": "haydar* AND 234*"
    }
  }
}

# similar with last one
POST test2/test/_search
{
  "query": {
    "query_string": {
      "query": "(tcNo.text:234* OR name.text:234*) AND (tcNo.text:haydar* OR name:haydar*)"
    }
  }
}

Yes I know all of your fields string. But I prefer use long or integer type for integer data. At the end of the queries, multi_match one is return no result but query_string return two correct result. So, you can use query_string query to search.