I have been digging into the documentation for a use of Substring that would allow me to define a substring based on a common delimiter in a field.

For instance I have a field in a data extension that is defined as name1_ - this field will always have a first and last name define in it. As a result I do not always have a constant fixed number of characters from which to apply substring as I see it defined in the Syntax Guide.

What I would like to do is this:

Name1_ = Dave Smith

From this field I would like to grab only the last name (Smith) and populate it as a variable for use elsewhere in my email. The one constant I do know of is that every time there will be a first and last name, and that I only want the last name in a variable.

I simply cannot find a sample application of this process in the ET documentation.

Ultimately it would end up like this:

%%[ Set @DIVLead = Substring(name1_ ,0,-1)Output(@DIVLead) ]%%

And @DIVLead would be Smith.


Give this a try:

VAR @name, @spacePosition, @lnamePosition, @lastName
SET @name = 'Dave Smith'
SET @name = Trim(@name)
SET @spacePosition = IndexOf(@name, ' ')
SET @lnamePosition = Add(@spacePosition,1)
SET @lastName = Substring(@name,@lnamePosition,50)

Here's what's happening. Pull the full name in and trim it to make sure there's no leading or trailing spaces. Find the index of the first space (Indexof(@name, ' ') - making sure there's a space in between the quotes. Add one to it to find the beginning position of the last name. Then use the substring function to pull the last name. I used 50 as the max length of the substring so if you have longer last names you'd want to make that bigger, but I figured that would do.

This even works on last names with two words (like Van Buren). If there are middle names it will pull those in too, but if you just have a first name then a space then a last name it should work. If you're worried about having two or more spaces between the first and last name you can just do a trim before you output the last name (SET @lastName = Trim(@lastName)).

I hope that helps.

| improve this answer | |

You should be able to use the IndexOf function in conjunction with Substring to do what you need. I would also put name1_ in its own variable, as at one point there was (and may still be ) an issue with doing manipulation on fields at send time.

  SET @name1_ = name1_
  Set @DIVLead = Substring(@name1_, IndexOf(@name1_," "), Length(@name1)))

Good luck!

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.