# Clumsy replace

**URL:** <https://forum.certara.com/t/clumsy-replace/2805>\
**Category:** Phoenix WNL basics\
**Created:** [December 5, 2014, 6:51pm UTC](https://forum.certara.com/t/clumsy-replace/2805 "2014-12-05T18:51:15Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Helmut\_Schuetz](https://avatars.discourse-cdn.com/v4/letter/h/eb8c5e/32.png) [@Helmut\_Schuetz](https://forum.certara.com/u/Helmut_Schuetz)\
**Post date:** [December 5, 2014, 6:51pm UTC](https://forum.certara.com/t/clumsy-replace/2805/1 "2014-12-05T18:51:15Z")

</div>

Hi,

I have a column “Var” with rows containing strings of the pattern “foo\_bar”. “foo” and ”bar” are of variable lengths and I want to retain only the “bar”-part. I couldn’t find a way to replace “foo\_“ with ”nothing” (in a filter?). 😉

Now I have a custom transformation

```auto
mid(Var, search(Var, '_', 1)+1, length(Var)-search(Var, '_', 1))

```

which is working but ugly. Is there are more elegant setup?

---

<div class="post-metadata">

**Author:** ![ana.henry](https://sea1.discourse-cdn.com/certara/user_avatar/forum.certara.com/ana.henry/32/2578_2.png) [@ana.henry](https://forum.certara.com/u/ana.henry)\
**Post date:** [December 5, 2014, 7:25pm UTC](https://forum.certara.com/t/clumsy-replace/2805/2 "2014-12-05T19:25:44Z")

</div>

Hi Helmut,

The most straight way is to do a simple ‘Filter’ action in the data wizard. Replace ‘foo\_bar’ for ‘bar’. Is there a reason you might need to use a custom transformation?

For a custom transformation, in your case you can also use

replace(var,1,4, “”)

or

substitute(var,“\_bar”,“”)

Thanks,

Ana

---

<div class="post-metadata">

**Author:** ![Helmut\_Schuetz](https://avatars.discourse-cdn.com/v4/letter/h/eb8c5e/32.png) [@Helmut\_Schuetz](https://forum.certara.com/u/Helmut_Schuetz)\
**Post date:** [December 5, 2014, 8:12pm UTC](https://forum.certara.com/t/clumsy-replace/2805/3 "2014-12-05T20:12:30Z")

</div>

Hi Ana,

> [@ana.henry](#):
>
> The most straight way is to do a simple ‘Filter’ action in the data wizard. Replace ‘foo\_bar’ for ‘bar’.
> 
> Is there a reason you might need to use a custom transformation?

Both foo and bar are variable, eg., foo (formulation, treament…) and bar (T, R, A…) I want to avoid manual entries which seem to be necessary in filters.

> For a custom transformation, in your case you can also use
> 
> ```auto
> replace(var,1,4, "")
> or
> substitute(var,"_bar","")
> 
> ```

For the first one I do not know the number 4 beforehand and for the second on what “bar” actually is. 😉 I guess I have to stick to my clumsy formula.

---

<div class="post-metadata">

**Author:** ![ana.henry](https://sea1.discourse-cdn.com/certara/user_avatar/forum.certara.com/ana.henry/32/2578_2.png) [@ana.henry](https://forum.certara.com/u/ana.henry)\
**Post date:** [December 5, 2014, 9:16pm UTC](https://forum.certara.com/t/clumsy-replace/2805/4 "2014-12-05T21:16:58Z")

</div>

> [@helmut\_1](#):
>
> Hi Ana,
> 
> Both foo and bar are variable, eg., foo (formulation, treament…) and bar (T, R, A…) I want to avoid manual entries which seem to be necessary in filters.
> 
> For the first one I do not know the number 4 beforehand and for the second on what “bar” actually is. 😉 I guess I have to stick to my clumsy formula.

Another suggestion

replace(var, 1, search(var,“\_”,1),“”)

---

<div class="post-metadata">

**Author:** ![Helmut\_Schuetz](https://avatars.discourse-cdn.com/v4/letter/h/eb8c5e/32.png) [@Helmut\_Schuetz](https://forum.certara.com/u/Helmut_Schuetz)\
**Post date:** [December 6, 2014, 1:24am UTC](https://forum.certara.com/t/clumsy-replace/2805/5 "2014-12-06T01:24:58Z")

</div>

> [@ana.henry](#):
>
> Another suggestion
> 
> replace(var, 1, search(var,“\_”,1),“”)

Almost there. I don’t know the _length_ of ”bar” (can be \>1) – but

```auto
replace(var, 1, search(var, '_', length(var)-1), '')

```

works.

THX!
