# SQL (Kibana Canvas) - How to drop all text to the left of a string pattern **URL:** \ **Category:** Elasticsearch\ **Tags:** elastic-stack-sql\ **Created:** [May 26, 2022, 8:38pm UTC](https://discuss.elastic.co/t/sql-kibana-canvas-how-to-drop-all-text-to-the-left-of-a-string-pattern/305754 "2022-05-26T20:38:15Z")\ **Posts on this page:** 4\ **Page:** 1
**Author:** ![BenB196](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/benb196/32/83401_2.png) [@BenB196](https://discuss.elastic.co/u/BenB196)\ **Post date:** [May 26, 2022, 8:38pm UTC](https://discuss.elastic.co/t/sql-kibana-canvas-how-to-drop-all-text-to-the-left-of-a-string-pattern/305754/1 "2022-05-26T20:38:15Z")
Hi All, (Note: Asking under SQL even though the use-case is in Kibana Canvas, as the question pertains to ESSQL specifically.) I'm working on a Canvas setup and have the following query: ```auto SELECT kibana.alert.rule.name AS rule_name, kibana.alert.reason AS alert_reason FROM \".alerts-observability.metrics.alerts-*\" WHERE event.action = 'active' AND kibana.alert.workflow_status = 'open' AND kibana.alert.rule.name LIKE 'Remote_Office_%_Critical' AND \"@timestamp\" > NOW() - INTERVAL 60 MINUTE GROUP BY kibana.alert.rule.name, kibana.alert.reason ``` The value `alert_reason` always follows a similar pattern: `some text to the left for some text to the right`. I would like the query to be able to drop everything to the left up to and including the word `for` and have the value of `alert_reason` just be the text to the right of the word `for`. While I know I can drop text to the left via the [RIGHT function](https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-functions-string.html#sql-functions-string-right), I can't figure out how to get the `count` value dynamically drop everything to the left. (Note: The `count` needs to be handled dynamically as the text before and after `for` can be of inconsistent length). Looking online some other SQL implementations support things like `CHARINDEX` or `POSITION`, but it doesn't seem like ESSQL supports anything like this. Would anyone have any ideas on how to accomplish this? P.S. I know that runtime fields are support with ESSQL ([Use runtime fields | Elasticsearch Guide [8.2] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-runtime-fields.html)), but they are unfortunately not supported in Canvas, which makes runtime fields not an option. ---
**Author:** ![Lukas\_Wegmann](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/lukas_wegmann/32/97001_2.png) [@Lukas\_Wegmann](https://discuss.elastic.co/u/Lukas_Wegmann)\ **Post date:** [May 30, 2022, 6:11am UTC](https://discuss.elastic.co/t/sql-kibana-canvas-how-to-drop-all-text-to-the-left-of-a-string-pattern/305754/2 "2022-05-30T06:11:57Z")
Hi Ben I think the function you're looking for is [LOCATE](https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-functions-string.html#sql-functions-string-locate). Does that work for you? Cheers, Lukas ---
**Author:** ![BenB196](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/benb196/32/83401_2.png) [@BenB196](https://discuss.elastic.co/u/BenB196)\ **Post date:** [May 31, 2022, 1:40pm UTC](https://discuss.elastic.co/t/sql-kibana-canvas-how-to-drop-all-text-to-the-left-of-a-string-pattern/305754/3 "2022-05-31T13:40:59Z")
Thanks @Lukas_Wegmann `LOCATE` did the trick. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [June 28, 2022, 1:41pm UTC](https://discuss.elastic.co/t/sql-kibana-canvas-how-to-drop-all-text-to-the-left-of-a-string-pattern/305754/4 "2022-06-28T13:41:21Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.