Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Split Access Field

Posted on 2009-04-28
Medium Priority
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
  • 2
LVL 93

Accepted Solution

Patrick Matthews earned 2000 total points
ID: 24255808
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 43

Expert Comment

ID: 24255841
  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 43

Expert Comment

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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

572 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