Spllit Name Field

Posted on 2009-12-28
Last Modified: 2012-05-08
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.
Question by:SYOUNG
    LVL 18

    Accepted Solution

    How are you using split?

    Last Name

    First Name


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

    First Name is similar but taking the items after the ;
    LVL 28

    Expert Comment

    by:Bill Bach
    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.

    Author Closing Comment

    Split function works great!  The formula works too but I couldn't immediately figure out how to change the syntax for First Name.  Thanks!

    Author Comment

    Split function works great! The formula works too but I couldn't immediately figure out how to change the syntax for First Name. Thanks!

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
    Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now