Spllit Name Field

I need to split a single database field to LastName and FirstName.  The single database field stores the name as "LastName; FirstName" (semicolon is the delimiter).  I tried using the split function but it is not working for me.  Normally I'd just create a view but this data is coming from a Pervasive database I am not familiar with.
SYOUNGAsked:
Who is Participating?
 
UnifiedISCommented:
How are you using split?

Last Name
Split({YourField},";")[1]

First Name
Split({YourField},";")[2]

OR

Create a formula and enter this for Last Name:
IF INSTR({YourField}, ";") > 1 THEN
MID({YourField}, 1, INSTR({YourField}, ";") - 1)
ELSE
{YourField}

First Name is similar but taking the items after the ;
0
 
Bill BachPresidentCommented:
Pervasive PSQL doesn't support either SPLIT or INSTR.  However, it does have the LOCATE function, which you can use like this:

LastName -->  LEFT( NameField , LOCATE(NameField, ';') -1 )
FirstName -->  RIGHT( NameField, LENGTH(NameField) - LOCATE(NameField, ';') )

Note that the -1 on the LastName field means that if there is NO semicolon in the field, LOCATE returns 0, and a -1 gets passed to the LEFT function, which will likely spawn an error.  You may have to do a conditional test for the presence of the semicolon before doing that function call.
0
 
SYOUNGAuthor Commented:
Split function works great!  The formula works too but I couldn't immediately figure out how to change the syntax for First Name.  Thanks!
0
 
SYOUNGAuthor Commented:
Split function works great! The formula works too but I couldn't immediately figure out how to change the syntax for First Name. Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.