Split Access Field

Posted on 2009-04-28
Last Modified: 2012-05-06
i have a field called Field1 the value of field1 is


I need to seperate this field at the &

Results would be

Newfield1 = 41115455445151
Newfield2 = 2/16/2009
NewField3 = 123
NewField4 = demo
Question by:wellsuited
    LVL 92

    Accepted Solution

    If there are always 3 ampersands...

    SELECT Field1, Left(Field1, InStr(1, Field1, "&") - 1) AS Newfield1,
          Mid(Field1, InStr(1, Field1, "&") + 1, InStrRev(Field1, "&") - InStr(1, Field1, "&") - 1) AS Newfield2,
          Mid(Field1, InStrRev(Field1, "&") + 1) AS Newfield3
    FROM SomeTable
    LVL 41

    Expert Comment

      LEFT(Field1, CHARINDEX('&',Field1)-1) AS Newfield1,
      SUBSTRING(Field1, CHARINDEX('&',Field1)+1,
          CHARINDEX('&',Field1,CHARINDEX('&',Field1)+1)-CHARINDEX('&',Field1)-1) AS Newfield2,
      RIGHT(RTRIM(Field1), CHARINDEX('&',REVERSE(RTRIM(Field1)))-1) AS Newfield3
    FROM YourTable

    Of course, the NewField2 is of char type, to convert it to date you have to use CONVERT function.
    LVL 41

    Expert Comment

    Sorry, I forgot to tell: My solution works in MS SQL Server :-)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    737 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

    22 Experts available now in Live!

    Get 1:1 Help Now