Link to home
Start Free TrialLog in
Avatar of wellsuited
wellsuited

asked on

Split Access Field

i have a field called Field1 the value of field1 is

41115455445151&2/16/2009&123&demo

I need to seperate this field at the &

Results would be

Newfield1 = 41115455445151
Newfield2 = 2/16/2009
NewField3 = 123
NewField4 = demo
ASKER CERTIFIED SOLUTION
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SELECT
  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.
Sorry, I forgot to tell: My solution works in MS SQL Server :-)