Regex Expression

[Vaibhavj11]

Hi,

Need a small help on regex:-

I have a field called XXX having different length at row level. I want to remove all rows where "D" OR "D1234" is reflecting at the end of the rows.

For Example: 

10CXW7D

1810700640316637609073604000050058251

10DSTRD1234

I want to remove "D" & "D1234"  from end of the above rows.

Please suggest how to remove using Regex or any other way.

[BenMoss]

So remove anything after the last D?

Or in another way, keep everything before the last D?

Ben

[Vaibhavj11]

Yes you are correct.

Remove D from the last or keep before D

[BenMoss · Accepted solution]

I've chosen the 'Other Way' option...

Use a formula tool with the following statement, where Field1 represents the field you want to do this against.

LEFT([Field1],LENGTH([Field1])-

FINDSTRING(ReverseString([Field1]),"D")-1)

Essentially it finds the position of the last instance of the character D, and then gets us everything to the left of that.

 Ben

[Vaibhavj11]

Hi,

The formula is not working correctly. Incase of below record, it should be no change as D is not at the end. However, its showing like:

Input                        Output

10D5IKE                  10

Regards

[LJoonas]

One Quick fix for the formula could be to add an if sentence before the formula and only run it, if your condition (field ends in D OR D1234) is met. Something like this: 

IF Right([Field1], 5) = "D1234" OR Right([Field1], 1) = "D" THEN

LEFT([Field1],LENGTH([Field1])-

FINDSTRING(ReverseString([Field1]),"D")-1)

ELSE [Field1] ENDIF

[Thableaus]

Hi @Vaibhavj11 

This would be my REGEX solution:

REGEX_Replace([Field1], "^(.*)(D|D1234)$", "$1")