1

im trying to grab the value of a text field in a Data extension. It has a specifix syntax:

example_IWantToGrabThis_example

I want to grab the value between the underscores. I think i need to use CHARINDEX with Left and right but cannot seem to find examples that work in Marketing Cloud.

Kind regards Rob

Improve this question
2
  • I think you are looking for PatIndex salesforce.stackexchange.com/questions/308666/…
    – Jonas Lamberty
    Commented Mar 3, 2021 at 17:03
  • I disagree this is already answered. There are a ton of other options than the patindex that worked in that questions answer. And honestly those other options may actually be more performant in this situation. For instance the answer by Mateusz as well as my option for utilizing mock XML (gortonington.com/…). Just because there is a similar question with a relevant answer does not mean the question deserves to be closed (IMO).
    – Gortonington
    Commented Mar 5, 2021 at 14:53

1 Answer 1

Reset to default
2

As you need to capture the middle part and I assume that the part before _ and _ might be random, I believe that the best approach in SQL might be this abomination (exploded the code for better readability):

REPLACE(
    REPLACE(
        '1_Target_2', 
        LEFT('1_Target_2', CHARINDEX('_', '1_Target_2')),
        ''), 
    RIGHT('1_Target_2', CHARINDEX('_', '1_Target_2')),
    '') AS Target

I'm here capturing with LEFT and RIGHT parts you don't want and then delete them with REPLACE. For the use case, you shared I believe it should be more optimized then PATINDEX.

Depending on the exact use case you might either simplify it with an optional third argument of the CHARINDEX or complicate it with CASE to make the query safer. You can check examples of those possible extensions in my docs

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .