# Compare wildcard string to cell value and replace in canvas expression

**URL:** <https://discuss.elastic.co/t/compare-wildcard-string-to-cell-value-and-replace-in-canvas-expression/263234>\
**Category:** Kibana\
**Tags:** painless\
**Created:** [February 4, 2021, 11:35am UTC](https://discuss.elastic.co/t/compare-wildcard-string-to-cell-value-and-replace-in-canvas-expression/263234 "2021-02-04T11:35:06Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![fuzzyWedgie](https://avatars.discourse-cdn.com/v4/letter/f/d78d45/32.png) [@fuzzyWedgie](https://discuss.elastic.co/u/fuzzyWedgie)\
**Post date:** [February 4, 2021, 11:35am UTC](https://discuss.elastic.co/t/compare-wildcard-string-to-cell-value-and-replace-in-canvas-expression/263234/1 "2021-02-04T11:35:07Z")

</div>

How can I **replace** all cell values in a table in canvas if it matches a **wildcard string**?

I want to normalize some values in the table in a way that  
if a table cell **contains**"test abc" I want the cell value to be replaced by "Test a1"  
and if it **contains** value "test def", I want it to be replaced by " Test b2"  
etc.  
If none of them matches, Put " Not Mapped"

So I tried to do this but it doesnt work correctly, It now puts " Test a1" on all the cells:

Select  
category.field,  
count(\*) as events  
from index  
where "category" like ' %test%'  
group by category.field  
order by events

| mapColumn "normalized"  
fn={switch  
case={case if={getCell "category.field" | string "test abc" in "category.field"} then="Test a1"}  
case={case if={getCell "category.field" | string "test def" "category.field"} then="Test b2"}  
default="Not Mapped"}  
| table  
font={font align="left" color="#000000" family="Arial, sans-serif" italic=false size=10 underline=false weight="normal"} paginate=false  
| render

---

<div class="post-metadata">

**Author:** ![lukas](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/lukas/32/6812_2.png) [@lukas](https://discuss.elastic.co/u/lukas)\
**Post date:** [February 4, 2021, 6:57pm UTC](https://discuss.elastic.co/t/compare-wildcard-string-to-cell-value-and-replace-in-canvas-expression/263234/2 "2021-02-04T18:57:10Z")

</div>

You could modify your SQL clause to be something like this:

```sql
Select category.field, count(*) as events,
CASE
    WHEN category.field LIKE '%test abc%' THEN 'Test a1'
    WHEN category.field LIKE '%test def%' THEN 'Test b2'
    ELSE 'Not Mapped'
END AS mapped_field
from index
where "category" like ' %test%'
group by category.field
order by events

```

Let me know if that helps.

---

<div class="post-metadata">

**Author:** ![fuzzyWedgie](https://avatars.discourse-cdn.com/v4/letter/f/d78d45/32.png) [@fuzzyWedgie](https://discuss.elastic.co/u/fuzzyWedgie)\
**Post date:** [February 8, 2021, 1:58pm UTC](https://discuss.elastic.co/t/compare-wildcard-string-to-cell-value-and-replace-in-canvas-expression/263234/3 "2021-02-08T13:58:44Z")

</div>

Great that works! thnx.

---

<div class="post-metadata">

**Author:** ![fuzzyWedgie](https://avatars.discourse-cdn.com/v4/letter/f/d78d45/32.png) [@fuzzyWedgie](https://discuss.elastic.co/u/fuzzyWedgie)\
**Post date:** [February 8, 2021, 2:07pm UTC](https://discuss.elastic.co/t/compare-wildcard-string-to-cell-value-and-replace-in-canvas-expression/263234/4 "2021-02-08T14:07:37Z")

</div>

It works to remap the category names in the column but then it doesnt aggregate on the new mapped\_field.

---

<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:** [March 8, 2021, 2:08pm UTC](https://discuss.elastic.co/t/compare-wildcard-string-to-cell-value-and-replace-in-canvas-expression/263234/5 "2021-03-08T14:08:13Z")

</div>

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